Issue: Paste method of Worksheet class failed

Question

I've been working on a piece of work that aims to open and copy information from one worksheet and copies it into another. Here is the script

Sub Data_API()        
    Dim xlApp, xlBook        
    Dim xlBook2 As Workbook

    Set xlApp = CreateObject("Excel.Application")        
    Set xlBook = xlApp.Workbooks.Open("C:\Users\Pear\Book2.xlsm")        
    Set xlBook2 = Workbooks("Extract.xlsb")

    xlApp.Visible = True        
    xlApp.Run "ImportFile"

    xlBook.Worksheets("Sheet1").Range("A2:K500").Copy        
    xlBook2.Activate

    ActiveSheet.Paste Destination:=Worksheets("Data").Range("A2")

    xlBook.Application.CutCopyMode = False   
    xlBook.Close False

    Application.DisplayAlerts = False
End Sub

I receive an error message as follow:

Run-time error 1004: Paste Method of worksheet class failed.

When I select debug it directs me to this line of code

ActiveSheet.Paste Destination:=Worksheets("Data").Range("A2")

Any thoughts/feedback/suggested changes would be much appreciated.

Possible duplicate of Paste method of worksheet class failed. Also, your code is VBA, not VBScript. — Ansgar Wiechers
@A Lim please clarify if this code is VBSrcipt or VBA, as your Q was originally tagged with both. There are (resolvable) ssues either way — chris neilsen
@chrisneilsen The code is obviously VBA, and is also run as such, otherwise they'd be seeing a syntax error rather than a runtime error. — Ansgar Wiechers

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-01-15T09:28:26

Don't use .Activate see How to avoid using Select in Excel VBA, technique for Activate is the same.

Instead copy in one statement, and specify in which workbook Worksheets("Data") is:

xlBook.Worksheets("Sheet1").Range("A2:K500").Copy Destination:=xlBook2.Worksheets("Data").Range("A2")

Also you don't need to open anther Excel instance. Try the following:

Sub Data_API()        
    Dim wbSrc As Workbook        
    Set wbSrc = Workbooks.Open("C:\Users\Pear\Book2.xlsm")        

    Dim wbDest As Workbook
    Set wbDest = Workbooks("Extract.xlsb") 'assumes Extract.xlsb is already open

    Application.Run "ImportFile"

    wbSrc.Worksheets("Sheet1").Range("A2:K500").Copy Destination:=wbDest.Worksheets("Data").Range("A2")

    Application.CutCopyMode = False   
    wbSrc.Close False

    'Application.DisplayAlerts = False 'why this here?
End Sub

Issue: Paste method of Worksheet class failed

1 Answers