Copying a range to a separate excel database
 

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").Curr entRegion
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").Curr entRegion

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

Copying a range to a separate excel database
 

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

destrange.Entirerow.delete

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
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").Curr entRegion
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").Curr entRegion

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