Hi,

I am using Rons code to copy a range from one workbook to another and it
works well. The problem I am having is that the source range is not always
the same number of rows but will always have the same number of columns. So I
am getting blank rows inserted into destination workbook (which happens to be
a LIST). My source range is A2:AA1000, How can I change the code below so
that my source range will only copy to the last row of data.

Your help appreciated as always

Thanks
Winnie




Sub Copy_To_DATA2009_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("DATA 2009.xls") Then
Set DestWB = Workbooks("DATA 2009.xls")
Else
Set DestWB = Workbooks.Open("C:\Spares sales\DATA 2009.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("SALES1").Range("A2:AA1000")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("2009")


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True

End With
End Sub