If/Then Loop To Copy/Paste to a Different Worksheet

[danders]

Hello:

I need some help please with creating a VBA sub procedure. I need to create an If/Thenloop that copies data from field A2 of one worksheet, goes to another worksheetand paste the data in cell S2, calls another sub procedure and then repeatsthese steps. I’ve explained it a little more below:


I have a workbook with two worksheets; “Auth Form” and “Contacts”. The “Contacts”worksheet has contact names in column A and contact emails in column B. I needto create an If Then Loop where if the field in column A of the “Contacts”worksheet is not blank, the data is copied from that field and paste in fieldS2 of the “Auth Form” worksheet. The sub procedure would then call another subprocedure to execute. Once the other sub procedure has executed, it would returnto the Contacts” worksheet, move to cell A3, repeat the copy/paste to field S2of the “Auth Form” worksheet and then call the other sub procedure to executeagain. This would continue until it encountered a blank field in column A. Here’swhat I have come up with:
Sub Email_Loop()
Dim i As Long
i = 1
Do While Cells(i,"A").Value <> ""
If Cells(i,"A").Value <> "" Then
‘Don’t know what to put here to copy the data and paste itinto cell S2 it into the other worksheet’

Call Mail_ActiveSheet
End If
i = i + 1
Loop

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Email_Loop()
Dim Cl As Range

For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
   If Cl.Value <> "" Then
      Sheets("Auth Form").Range("S2").Value = Cl.Value
      Call Mail_ActiveSheet
   End If
Next Cl
End Sub

 

[danders]

Chippenham:
Hello, and thank you! This almost gets me there. The code you've provided does copy the data from field A2 on the "Contacts" worksheet and pastes it into field S2 of the "Auth Form" worksheet, but it doesn't loop afterwards and copy the data from cells A3, A4, A5 etc. Are we able to make it loop after it calls Mail_ActiveSheet and repeat the Copy/Paste/Call?

 

[danders]

Actually, the code is not copying the data from column A of the "Contacts" worksheet at all. I thought it worked earlier because field S2 of the "Auth Code" worksheet was pre-populated from yesterday, but once i cleared that field, i could tell the code was not pasting to that field.

 

[danders]

I stand corrected; the code absolutely works! I am uncertain what the issue was earlier, but I have run it multiple times since, and it is working flawlessly. Thank you, Chippenham; You have been a great help!

 

[Fluff]

Glad to help & thanks for the feedback