Copy range from one workbook to another

[insomniac_ut]

Hello,

I'm trying to copy a fixed dimension range from one workbook to the end of a worksheet in another workbook.

This is the code I'm using to achieve this, though it's not particularly stable. I occasionally get a UsedRange of Object _Worksheet Failed or PasteSpecial Method of Range Class Failed error.

Is there something wrong with this code, or can someone suggest some improvements?

Thank you.

Public Sub CopyRanges()

    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim LastRowFinder As Long
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    
    Set wbSource = Workbooks.Open("C:\Users\xyz\Desktop\Source.xls")
    Set wsSource = wbSource.Worksheets("Sheet2")
    
    Set wbDest = Workbooks.Open("C:\Users\xyz\Desktop\Target.xls")
    Set wsDest = wbDest.Worksheets("Sheet2")
    
    wsSource.Range("B2:F40").Copy
    
    LastRowFinder = wsDest.UsedRange.Rows.Count
    wsDest.Range("B" & LastRowFinder + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Application.CutCopyMode = False
    
    wbDest.Save
    wbDest.Close
    
    wbSource.Save
    wbSource.Close
    
End Sub

 

[Norie]

Have you checked that using UsedRange to find the last row us actually giving the correct result?

Also, is the destination worksheet/ workbook protected?

If you are calling this code from outside Excel have you tried it from Excel?

 

[insomniac_ut]

Hi Norie,

1. UsedRange is managing to find the last row correctly
2. The destination workbook is not protected
3. That's just it - it sometimes throws errors from outside Excel. To right this, I open up Excel and run the macro (it runs correctly). I then save the sheet and try calling it from outside Excel and no problems.

This error is very sporadic/random. Out of curiosity, in my declarations should I be using New Workbook/Worksheet instead of just Workbook/Worksheet?

Thanks.

 

[Norie]

Can I ask how you are checking the last row is correct each time?

Are you debugging the code, perhaps stepping through it, adding watches for all the variables etc?

Oh, and no you shouldn't be using New.