VBA copy and paste in increment row number-CodePudding

I'm very new in VBA.

So far I made this script by using Record Macro command in Excel.

Sub Collecting()
'
' Collecting Macro
'
    Range("A1:L1").Copy
    Windows("Calculations.xlsx").Activate
    Range("W44").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AI44").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Results.xlsb").Activate
    Range("M1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'
End Sub

This is working, but I need the Range("A1:L1") and Range("M1") to be incremented automatically to Range("A2:L2") and Range("M2"), Range("A3:L3") and Range("M3"), and so on. Other 'ranges' than those are static.

Is there any way to do that? Many thanks in advance.

CodePudding user response：

You need a loop - google 'vba loop' to see examples and syntax and so on. An example might be

Dim rownum as long
For rownum = 1 to 30
    Range("A" & rownum & ":L" & rownum).copy
    'etc
Next

The above code would copy columns A:L incrementing from row 1 to row 30. Where your end row is variable, use either a Do While loop that stops when it identifies the end, or find the end row before starting the loop, save it in a variable, then start the loop like For rownum = 1 to endrow.