Macro to copy/paste specific range every 3 rows Solved/Closed

Question

Hello, 

I am beginner in VBA and now have an Excel sheet with about 4000 rows, where I could need a macro.

I already recorded a macro for the first rows, which looks as follow:

Sub Loop1()Range("BG2:FJ2").SelectSelection.CutRange("E3").SelectActiveSheet.PasteActiveWindow.SmallScroll ToRight:=39Range("BG3:DH3").SelectSelection.CutRange("E4").SelectActiveSheet.PasteRange("BG5:FJ5").SelectSelection.CutRange("E6").SelectActiveSheet.PasteActiveWindow.SmallScroll ToRight:=39Range("BG6:DH6").SelectSelection.CutRange("E7").SelectActiveSheet.PasteEnd Sub'Or alternatively for the first rowSub Loop2()Sheets("Worksheet1").Range("BG2:FJ2").Cut Sheets("Worksheet1").Range("E3") Sheets("Worksheet1").Range("BG3:DH3").Cut Sheets("Worksheet1").Range("E4")End Sub

However, I do not know how to adjust it, so that Excel does the rest/loop until row 4000.

Any idea or help would be highly appreciated.

Thanks a lot in advance.

Best,
Isa

TrowaD · Answer

Hi Isa,

See if the following code works for you:
Sub RunMe()
Dim x As Integer
x = 2
Do
    Range(Cells(x, "BG"), Cells(x, "FJ")).Cut Sheets("Worksheet1").Cells(x + 1, "E")
    Range(Cells(x + 1, "BG"), Cells(x + 1, "DH")).Cut Sheets("Worksheet1").Cells(x + 2, "E")
    x = x + 3
Loop Until x > 4000
End Sub 

Best regards,
Trowa

Macro to copy/paste specific range every 3 rows

1 response

Similar discussions

Subscribe To Our Newsletter!