Copying a range to a separate excel database

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
 
R

Ron de Bruin

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

destrange.Entirerow.delete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top