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

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.
--
Carlee

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

One way:

Sub CopyBottomRow()

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

End Sub

Regards

Trevor


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

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

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?
--
Carlee


"Trevor Shuttleworth" wrote:

One way:

Sub CopyBottomRow()

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

End Sub

Regards

Trevor


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

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

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

On Sat, 7 Jul 2007 15:48:00 -0700, Carlee
wrote:

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.

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

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

Carlee wrote:

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?
--
Carlee

"Trevor Shuttleworth" wrote:

One way:

Sub CopyBottomRow()

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

End Sub

Regards

Trevor


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




--

Dave Peterson