Nested copy/paste loops in excel VBA

Question

Very new to VBA and trying to write a macro that has a loop within a loop. I'd like the whole code to run 30 times

For i = 1 To 30 Worksheets("Salary Extract (SPs Only)").Range("B" & (1 + i)).Copy Worksheets("Pitcher Matchup Analysis").Paste Destination = Worksheets("Pitcher Matchup Analysis").Range("B9") For i = 1 To 9 Worksheets("Pitcher Matchup Analysis").Range("B" & (38 + i)).Copy Worksheets("Batter Matchup Analysis").Paste Destination = Worksheets("Batter Matchup Analysis").Range("B1") Worksheets("Batter Matchup Analysis").Range("B88:AC88").Copy Worksheets("Pitcher Matchup Analysis").Range("C" & (38 + i) & "AD" & (38 + i)).PasteSpecial xlPasteValues Next i Worksheets("Pitcher Matchup Analysis").Range("A71:R71").Copy Worksheets("Pitcher Comparison").Range("A" & (1 + i) & "R" & (1 + i)).PasteSpecial xlPasteValues Next i End Sub

Within each loop I'd like this block to loop 9 times

For i = 1 To 9 Worksheets("Pitcher Matchup Analysis").Range("B" & (38 + i)).Copy Worksheets("Batter Matchup Analysis").Paste Destination = Worksheets("Batter Matchup Analysis").Range("B1") Worksheets("Batter Matchup Analysis").Range("B88:AC88").Copy Worksheets("Pitcher Matchup Analysis").Range("C" & (38 + i) & "AD" & (38 + i)).PasteSpecial xlPasteValues Next i

My guess is this is a fairly simple fix but I'm not sure and couldn't figure it out looking at other questions.

Just place the second loop inside the first loop and rename i of the the second loop to something else. — VBA Pete
– VBA Pete, Commented Jan 13, 2017 at 0:07

Dash · Accepted Answer · 2017-01-13 00:06:21Z

At first glance the issue is that you can't use the counter "i" for both loops. Change one of the counters to "j" and all references in the inner loop to "j" as well. I'm not sure if there are other problems but that one jumps out at me.

Collectives™ on Stack Overflow

Nested copy/paste loops in excel VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related