Excel Loop question

[leong82]

Hi everyone,

I need help on a code that I writing. Basically the excel worksheet has 2 tabs. Tab 1 is called Client List and the tab 2 is called Email.
The code will go to “Client List”, copy from A2 and past the data into A4 in the tab “Email”. It will then run the macro Email_eDealer.
However, I need this to loop and after completing the macro, it goes to “Client List” again and copy A3 (1 line below A2), past the data into A4 in the tab “Email”. It will then run the macro Email_eDealer. From A3 it will go to A4, then A5 so on and so forth until it reaches a blank cell.

Here is the code without the loop function

Sub Macro2()
'
' Macro2 Macro
'
'
Sheets("Client List").Select
Range("A2").Select
Selection.Copy
Sheets("Email").Select
Range("A4").Select
ActiveSheet.Paste

Call Email_eDealer

End sub

Any help would be great. I roughly know I need a loop function but I am just so new at this. Thanks everyone.

 

[FormR]

Hi, welcome to the board!

You could try something like this..

Sub Macro2()
Dim i As Long
For i = 2 To Sheets("Client List").Range("A" & Sheets("Client List").Rows.Count).End(xlUp).Row
  Sheets("Client List").Range("A" & i).Copy Destination:=Sheets("Email").Range("A4")
  Call Email_eDealer
Next i
End Sub

 

[leong82]

Hi, welcome to the board!

You could try something like this..

Sub Macro2()
Dim i As Long
For i = 2 To Sheets("Client List").Range("A" & Sheets("Client List").Rows.Count).End(xlUp).Row
  Sheets("Client List").Range("A" & i).Copy Destination:=Sheets("Email").Range("A4")
  Call Email_eDealer
Next i
End Sub

Yes it works! thank you very much for your help!