Copy last row with data to next empty row...

[Oreg]

Hi,


I'm really stuck!!!! I've come across Ron de Bruin's website, which
provides some great solutions for copying to other workbooks. Here's
where I'm stuck. I would like to set my sourcerange to the last row
that holds data and copy that row to another workbook on the next empty
row of sheet1. I'm so close I can taste it !...I think When
I run the macro, I'm getting Here's what I've got so far.


Sub CopytoSP_history()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Dim Lr1 As Long

Application.ScreenUpdating = False
If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVENT
TRACKER\TrackerLog\METRO\SPhistory\SPHistory.xls")
End If
Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1,
0).Row
Lr1 = Sheets("METRO").Range("B" & Rows.Count).End(xlUp).Offset(0,
0).Row

Set sourceRange = ThisWorkbook.Worksheets("SP").Range("B" & Lr1)
Set destrange = destWB.Worksheets("Sheet1").Range("B" & Lr)

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Thanks,

Oreg