Macro pasting on next available row

Question

I have a macro copying data from one spreadsheet and I want to paste it to the next available row in a master spreadsheet. However, it is pasting over the last row that I populated. Currently on row 620, I want it to paste on row 621 and then next time I run macro, row 622 and so on

In 2019, I will begin at row 3 on new spreadsheet.

Sub Paste()
'
' Paste Macro
'

'
    Range("B24:AQ24").Select
    Selection.copy
    Windows("Sales  - 2018.xlsx").Activate
    ActiveWindow.SmallScroll Down:=3
    Range("B620").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Windows( _
        "Invoice Company B Limited Partnership.xlsx" _
        ).Activate
End Sub

Not sure how to define next available row in the code.

Thanks for reading.

Please read [how to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|268.2975). — SJR, Dec 18 '18 at 14:52
You need to use last row +1, there are multiple examples, just do a search for "excel vba last row" — GMalc, Dec 18 '18 at 14:54
Thanks guys, I'll do some more research. Thanks for the link, SJR, — Phil, Dec 18 '18 at 14:59
You should start here: [Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)](https://www.youtube.com//watch?v=c8reU-H1PKQ&index=5&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5) — TinMan, Dec 18 '18 at 15:22

score 0 · Answer 1 · answered Dec 18 '18 at 15:05

Is it always B24:AQ24 that you're copying?

This will copy the range and paste onto the next blank row (based on data in column B) in Sheet2 of Sales - 2018.xlsx.

It's a single line of code with a line continuation ( _ ) at the end of the first line.

Public Sub Paste()

    ThisWorkbook.Worksheets("Sheet1").Range("B24:AQ24").Copy _
        Workbooks("Sales - 2018.xlsx").Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Offset(1)

End Sub

To begin on row 3 just add a heading into cell B2.

ThisWorkbook refers to the workbook containing the code.
You may want to change this to Workbooks("Invoice Company B Limited Partnership.xlsx") (which doesn't contain any code as it's xlsx rather than xlsm.)

Thanks Darren, works a treat! Thanks for taking the time. – Phil Dec 18 '18 at 15:25 — Phil, Dec 18 '18 at 15:25

Macro pasting on next available row

1 Answers1