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

--

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


"Albert" <(E-Mail Removed)> wrote in message news:BC8AA3BE-00D9-4828-B2AD-(E-Mail Removed)...
> 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