copy_paste to the first blank row

[RLY]

A simple macro copies range A2:E7 from sheet1 & pastes to A2:E7 of sheet2.
If the same macro is selected a 2nd time, I need it to copy the same values
from sheet1, but paste to the first blank row avail on sheet 2 (not overwrite
the existing data). Same thing if selected a third time...
I just can't quite get it to work properly - Please help.
Thanks, Robert

 

[Don Guillett]

sub copytonextavailrow()
with sheets("sheet2")
lr=.cells(rows.count,"a").end(xlup).row+1
sheets("sheet1").range("a2:e27").copy .cells(lr,"a")
end with
end sub

 

[Mike H]

Hi,

Try this

Sub sonic()
lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("A2:E7").Copy _
Destination:=Sheets("Sheet2").Range("A" & lastrow)
End Sub

Mike

 

[Jacob Skaria]

Try the below

Sub MyMacro()
Dim lngRow As Long
lngRow = Sheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Sheets("Sheet1").Range("A2:E7").Copy _
Sheets("Sheet2").Range("A" & lngRow)
End Sub

If this post helps click Yes

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy1.htm