need help with multiple loops

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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:
Code:
    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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Like this?
Code:
    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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,122
Messages
5,599,830
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top