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

3

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.

answered Jan 13, 2017 at 0:06

Dash

312 bronze badges

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

Collectives™ on Stack Overflow

Nested copy/paste loops in excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related