Copy and paste while incrementing row number

Question

I made this script by using the 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.

You need the row number to be incremented but where does it stop? Is there a fixed row number (e.g. row number 1 to 500) or if the code needs to find the last row number, which column should it refer to? You are using a lot of `Select`/`Activate` which is very prone to error, you are encouraged to read on [how to avoid using select](https://stackoverflow.com/a/10717999/15597936). Is `Results.xlsb` the workbook that this code is running on? — Raymond Wu, May 26 '22 at 05:49
It will stop at the last row with data. I already found how to do it using loop., and yes, thank you for the information on 'how to avoid using select'. — Richie Adiyat, May 27 '22 at 06:05

score 0 · Accepted Answer · answered May 26 '22 at 06:00

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.

Copy and paste while incrementing row number

1 Answers1