Copy last row from sheet 1 to last row in sheet 2

[Guest]

Hi all,
Can anyone provide some code that will:

1. Copy the last completed row in SHeet 1, and
2. Paste the copied row to the next available blank row in Sheet 2 of the
same workbook?

Many thanks.

 

[Trevor Shuttleworth]

One way:

Sub CopyBottomRow()

Sheets("sheet1").Range("a65536").End(xlUp).EntireRow.Copy _
Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0)

End Sub

Regards

Trevor

 

[Guest]

I there,
the code you provided works, however, when i use it a second time to copy
and paste a new row of data into sheet 2, my previous rows get over written
with the new data.

Is it possible for the code you provided to paste any new rows to the next
available blank row, there by creating a list of pasted rows over time?

 

[Gord Dibben]

Sub findbottom_paste()

Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) _
.EntireRow
Set rng2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng1.Copy Destination:=rng2
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Trevor's code assumes that the row to be copied and the row to be pasted can
both be determined by looking at column A.

Can you pick out a column that always has data in it if that row is used? If
yes, then you could modify Trevor's code (or Gord's code) to use that column.

Option Explicit
Sub Testme01()
dim myCol as string
dim RngToCopy as range
dim DestCell as range

'what column can you use--I used X.
myCol = "X"

with worksheets("sheet1")
set rngtocopy = .cells(.rows.count,mycol).end(xlup).entirerow
end with

with worksheets("sheet2")
set destcell = .cells(.rows.count,mycol).end(xlup).offset(1,0) _
.entirerow.cells(1)
end with

rngtocopy.copy _
destination:=destcell

end sub