VBA to Copy Rows Until Blank Row and Paste Data on First Blank Row

[melissa1113]

I am trying to create VBA for a macro in which my excel spreadsheet will always have data in Columns A-W but will have an undetermined row count. I need the macro to copy the rows with data and then paste those rows into the first blank row. The first row is always my header row. For example, I have data in rows 2-8 so I would want the macro to copy rows 2-8 and paste the data starting on row 9. The next time I use the macro, I may only have data in rows 2-4 so I need it to copy those rows and paste the data beginning on row 5. Can someone help?

[alansidman]

Try this:

Option Explicit
Sub Mel()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    Range("A2:W" & lr).Copy Range("A" & lr + 1)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox ("task complete")

End Sub

[melissa1113]

That worked, thank you very much!