CODEWITHSUNDEEP
vbaexcel
ejjhayes
ejjhayes

Reputation: 1

Excel macro copy loop

I am trying to create a looped copy-paste macro.

What I'm trying to do is copy A8 to A9, advance 7 rows and copy A16 to A17, advance 7 rows and copy A24 to A25. I need to repeat the same pattern up to row 10,000 otherwise I would write it manually as below. Normally I would do this with a formula but because that would create a bunch of circular references on the sheet that's unfortunately not a feasible solution.

Range("A8").Copy
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Range("A16").Copy
Range("A17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False     

Range("A24").Copy
Range("A25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Upvotes: 0

Views: 60

Answers (1)

SJR
SJR

Reputation: 23081

It's a bit quicker to transfer the value directly rather than copying and pasting.

Sub x()

Dim r As Long

For r = 8 To 10000 Step 8
    Cells(r + 1, 1).Value = Cells(r, 1).Value
Next r

End Sub

Upvotes: 2

Related Questions