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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RLY" wrote in message
...
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

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

"RLY" wrote:

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

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
---------------
Jacob Skaria


"RLY" wrote:

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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"RLY" wrote in message ...
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