What is the best way to find last row and paste data below it?

[egghi]

Hi,

I am some problem copying data from one sheet and paste it to the first empty row on another sheet.

My code:

Sub findlastrow()

Dim lRow As Long
Dim Range As Range

' Find the FIRST EMPTY row by adding 1 to the last row
With ActiveSheet
' Determine last row
lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

' Paste data
Windows("Book2").Activate
Rows("1:2000").Select
Selection.Copy
Windows("insert first empty row.xls").Activate
Range("lRow").Select
ActiveSheet.Paste
End With

End Sub

Thanks in advance!

 

[GlennUK]

Have a look at this, and see if it helps:

http://www.tek-tips.com/faqs.cfm?fid=2112

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AnotherHiggins]

There is also faq707-2115.

But if you have a column that you know will not be empty on the last row, then I use this:

LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Offset(1).Row

(Replace "A" with the appropriate column)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

You could really shorten that down ...

Sub CopyToLastRow()
    Dim lRow As Long
    lRow = Cells.Find("*", after:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Workbooks("Book2").Sheets("Sheet1").Range("1:2000").Copy Workbooks("insert first empty row.xls").Sheets("Sheet1").Rows(lRow)
    Application.CutCopyMode = False
End Sub

Don't forget to set your sheet names accordingly.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci