copy_paste to the first blank row

  • Thread starter Thread starter RLY
  • Start date Start date
R

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
 
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
 
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
 
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
 
Back
Top