Runtime error 1004 PasteSpecial method of Range class failed. (1 Viewer)

[dark11984]

Hi, I am trying to copy from one workbook and paste into another workbook but keep encountering a Runtime error 1004 PasteSpecial method of Range class failed.

The workbook I am pasting into is protected and I don't have the password to unprotect so not sure if this is causing the error? I am able to paste the data manually with no issues.

I have read other forums where it is suggested that there is no data to copy but i can see that the data has been copied when I debug and look at the open workbook.

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook2 = objXLApp.Workbooks.Open("Workbook2 Location")
objXLApp.Application.Visible = True
objXLBook2.worksheets("OrderHeader").select
objXLBook2.worksheets("OrderHeader").range("A5:Q500").clearcontents
objXLBook2.worksheets("OrderLine").select
objXLBook2.worksheets("OrderLine").range("A5:J500").clearcontents

Set objXLBook = objXLApp.Workbooks.Open("Workbook1 Location")
objXLApp.Application.Visible = True
objXLBook.worksheets("Order_Hdr").range("A2:Q50").copy
objXLBook2.worksheets("OrderHeader").range("A5").PasteSpecial xlPasteValues

objXLBook.worksheets("Order_Ln").range("A2:J50").copy
objXLBook2.worksheets("OrderLine").range("A5").PasteSpecial xlPasteValues

objXLApp.Application.displayalerts = False
objXLBook2.SaveAs ("Workbook2 Location")
objXLApp.Quit
objXLBook2 = objXLApp.Workbooks.Open("Workbook1 Location")
objXLApp.Application.Visible = True
objXLBook.SaveAs ("Workbook1 Location")
objXLApp.Quit
objXLApp.Application.displayalerts = True

Thanks in advance.

 

[Isaac]

You can't paste until that Worksheet is activated

 

[Isaac]

But that's really the wrong way to go about it - doing the Select and Activate. You should assign values directly instead.

But try it like you have it after first using Worksheet.Activate (and replace all your worksheet select's with worksheet activate)