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!