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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,234
Members
430,198
Latest member
KitaYama

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