VBA: Nested For/Loop

Question

I am creating a macro that will copy all data from one workbook/multiple sheets into another workbook/multiple sheets. The first spreadsheet has 7 worksheets named as Sun-Sat. The second worksheet has 10 worksheets 3 worksheets are irrelevant and the other 7 worksheets are named Sunday-Saturday.

I tested each for loop separately and they work as needed. When trying to combine them the inner for statement repeats and cycles through all dates before backing out. I have tried incorporating a exit for to jump out of the inner for but when going back to the inner for it does not increment +1 to go to the next date. Is there a simple way to add +1 from the outer for statement?

enter code here
Dim wsShortDays, wsFullDays As Variant
Dim wsShortDaysCrnt, wsFullDaysCrnt As Long
Dim SD, FD As Long

wsShortDays = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
wsFullDays = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

For FD = LBound(wsFullDays) To UBound(wsFullDays)
    With wbk1.Worksheets(wsFullDays(FD))

        For SD = LBound(wsShortDays) To UBound(wsShortDays)
            With wbk2.Worksheets(wsShortDays(SD))
                wbk2.Worksheets(wsShortDays(SD)).Activate
                Range("A:H").Copy
            End With
            Exit For
        Next SD


        wbk1.Worksheets(wsFullDays(FD)).Activate
        Range("C:J").PasteSpecial xlPasteAllUsingSourceTheme
        SD = 1
    End With
Next FD

Scott Craner · Accepted Answer · 2016-09-07 17:56:47Z

2

You do not need the inner loop as your arrays are synced just use the same reference number from the first loop. It will equate Sunday with Sun and so forth:

Dim wsShortDays As Variant, wsFullDays As Variant
Dim FD As Long

wsShortDays = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
wsFullDays = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

For FD = LBound(wsFullDays) To UBound(wsFullDays)
   wbk2.Worksheets(wsShortDays(FD)).Range("A:H").Copy
   wbk1.Worksheets(wsFullDays(FD)).Range("C:J").PasteSpecial xlPasteAllUsingSourceTheme
Next FD

answered Sep 7, 2016 at 17:56

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tyeler Over a year ago

you beat me to it! I had an explanation with mine also saying that excel isn't capable of stand-alone multi-threading which is why his nested for statements felt counter-intuitive to him. Our codes are 1:1 identical though.

c3nixon Over a year ago

Thanks Scott - I was really overcomplicating it!

Collectives™ on Stack Overflow

VBA: Nested For/Loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related