0

Very new to VBA and trying to do a simply copy/paste special loop. How would you write the code so that each time it loops it copies one cell down in the Filter Out Pitchers tab and pastes special one cell down in the Batter Comparison tab?

Sub Hitters()

    For i = 1 To 500
        Worksheets("Filter Out Pitchers").Range("B2").Copy
        Worksheets("Batter Analysis").Paste _
        Destination:=Worksheets("Batter Analysis").Range("B1")
        Worksheets("Batter Analysis").Range("A88:AA88").Copy
        Worksheets("Batter Comparison").Range("A2:AA2").PasteSpecial xlPasteValues
    Next i
End Sub
Community
  • 1
  • 1
D. Day
  • 1
  • `Batter Comparison` or `Batter Analysis`? you want to copy rows or only from column B? not clear yet. – A.S.H Jan 11 '17 at 00:52
  • I want it to do the same thing each time in the Batter Analysis tab, and paste special one row down in the Batter Comparison tab. – D. Day Jan 11 '17 at 01:16
  • It would copy cell B2 in the Filter Out Pitcher tab, paste into Cell B1 of the Batter Analysis tab, and Copy/Paste as values the range specified into the Batter Comparison tab. The things that would change in the 2nd loop would be that I would want it to copy cell B3 from the Filter Out Pitchers tab, and paste one row down in the Batter Comparison tab – D. Day Jan 11 '17 at 01:19
  • Is it OK to copy the value from B2 (and B3, B4, etc) of "Filter Out Pitchers" to B1 of "Batter Analysis"? Or do those cells contain a formula and/or formatting that needs to be copied? If you need to copy formulae / formatting, the answer by @A.S.H is the way to go, but it could be improved slightly if you only need to copy a value for that cell. ("Improvement" = "bypass copy/paste") – YowE3K Jan 11 '17 at 01:46

1 Answers1

2

Not sure I understood completely, but this may be what you're after:

For i = 1 To 500
    Worksheets("Filter Out Pitchers").Range("B" & (1+i)).Copy _
        Destination:=Worksheets("Batter Analysis").Range("B2")
    Worksheets("Batter Comparison").Range("A" & (1+i) & ":AA" & (1+i)).Value = _
        Worksheets("Batter Analysis").Range("A88:AA88").Value
Next i
A.S.H
  • 29,101
  • 5
  • 23
  • 50