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