need help with multiple loops

[sous2817]

Hello Everyone,

I have two lists. In Sheet1 column D I have a list of doctors and in Sheet 1column E I have a list of assistants. I'm trying to customize a letter in Sheet4 to greet the doctor and their assistant. The problem I'm having is, the code runs through D1 all of column E, then D2 all of column E, D3 all of column E, etc, etc, etc. I need it to loop D1 and E1, then D2 and E2, D3 and E3, etc. until the end of the list.

The code I have so far is:

    For Each t In Range("D1", Range("D60000").End(xlUp))
    t2 = t.Value
    Worksheets("Sheet4").Range("A1").Value = "Dr. " & t2
    For Each b In Range("E1", Range("E60000").End(xlUp))
    b2 = b.Value
    Worksheets("Sheet4").Range("C1").Value = "Mr. or Ms." & b2
    Worksheets("Sheet4").Range("A1:G1000").Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="C:\Junk\" & t2, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWindow.Close
    Next b
    Next t

Any help you can give me would be greatly appreciated.

Kind regards,
sous2817

 

[jindon]

Like this?

    Dim t As Range
    For Each t In Range("D1", Range("D60000").End(xlUp))
        Worksheets("Sheet4").Range("A1").Value = "Dr. " & t.Value
        Worksheets("Sheet4").Range("C1").Value = "Mr. or Ms." & t.Offset(,1).Value
        Worksheets("Sheet4").Range("A1:G1000").Copy
        Workbooks.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs Filename:="C:\Junk\" & t.Value, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        ActiveWindow.Close
    Next t

 

[sous2817]

Jindon,

Thank you very much! Yes, exactly like that! I was working on using an i variable and looping through it a bunch of times using "D" & i and "E" and i references. Your way is much, much cleaner and faster.

Thanks again!