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](https://stackoverflow.com/questions/41251695/paste-method-of-worksheet-class-failed). Also, your code is VBA, not VBScript. — Ansgar Wiechers, Jan 15 '19 at 09:29
@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, Jan 15 '19 at 09:52
@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, Jan 15 '19 at 16:22

Pᴇʜ · Answer 1 · 2019-01-15T10:20:57.443

0

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

edited Jan 15 '19 at 10:20

answered Jan 15 '19 at 09:28

Pᴇʜ

56,719
10
49
73

Hi Peh, Thanks for your help. I have implemented your suggestion but get the following error message: "Run-time error 1004: Copy method of range class failed" Any thoughts? – A. Lim Jan 15 '19 at 09:59
Can you please confirm first if you are using [tag:vba] or [tag:vbscript]? – Pᴇʜ Jan 15 '19 at 10:05
Apologies for my lack of understanding. I believe i am using VBA – A. Lim Jan 15 '19 at 10:08
@A.Lim Please try the code in my edited answer. Also make sure there are no merged cells in the area you try to paste in. – Pᴇʜ Jan 15 '19 at 10:21
@A.Lim: if you are using an editor in Excel to write your code, you are using VBA. If not, likelihood is that you are using vbscript – Zac Jan 15 '19 at 11:59

Issue: Paste method of Worksheet class failed

1 Answers1