Copying a range to a separate excel database

  • Thread starter Thread starter Albert
  • Start date Start date
A

Albert

Hi Guys,

I am using Ron de Bruin's code to copy "usedcells" to a excel
database/masterfile.
This is what I got so far:
Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Test DB.xlsm") Then
Set destWB = Workbooks("Test DB.xlsm")
Else
Set destWB = Workbooks.Open("C:\Documents and Settings\albertr\My
Documents\Test Database\Test DB.xlsm")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange =
ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

The problem I have that when I execute the command the sourcerange is
selected in the destination sheet as this is active
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion

How can I correct this to copy all used rows except the first row in the
source file and then paste it into the last row of the destination sheet?

Thanks
Albert
 
One way is to delete the first row after the copy/Paste special code

destrange.Entirerow.delete
 
Back
Top