Execute Macro while using listed name for each run.

[Turbo68]

I have a macro that I want to run automatically using a list in AD9 thru AD23 ( 20 names). The Macro uses the name to sort a sheet and gets it ready to email. What I am trying to do is to get it to use the list starting with the first name in AD9 and copy it to A2 then run the macro 1. Then, when it is finished copy the 2nd name into A2 and repeat macro 1. Doing this all thru my list till blank space ( needed for further additions) and then exiting macro.

Thank in advance.

 

[PatOBrien198]

Sub ProcessList()

Dim rng As Range
Dim r As Long
Set rng = ActiveSheet.Range("AD9")
r = 0
Do While rng.Offset(r, 0) <> ""
    Range("A2") = rng.Offset(r, 0)
    ' run your macro here
    r = r + 1
Loop
MsgBox "Done.", vbExclamation
End Sub