Excel macro copy loop

Question

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

SJR · Accepted Answer · 2017-11-02 15:59:22Z

2

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

answered Nov 2, 2017 at 15:59

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

1 Comment

ejjhayes Over a year ago

That works great and is much faster than my solution. Thanks.

Collectives™ on Stack Overflow

Excel macro copy loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related