VBA run-time error 9 & Runtime Error 429 - ActiveX Component Can't Create Object

[Apple1]

Using Excel 2010.

I have this simple piece of code


With ThisWorkbook.Sheets("AA")
.Cells.Copy 'Replace the sheet with values
.Range("A1").PasteSpecial Paste:=xlPasteValues
End With

It works when I use it in other workbook.

But for this particular workbook when it is part of a larger code I get this error:
"Runtime error 9 "subscript out of range"

When I use it as a standalone, I get the error:
"Runtime Error 429 - ActiveX Component Can't Create Object"

I also get the error: "Runtime Error 429 - ActiveX Component Can't Create Object"
when I run other code.

Could there be something wrong with the particular workbook? The workbook was spoil before when I ran a macro.

Thank you

 

[CharlesChuckieCharles]

The only bit in your simple code which could cause a Subscript error is Sheets("AA") there has to be a Sheet called AA in any work book to get this code to work,

all the other messages suggest other stuff, not posted here, is going on

 

[Domenic]

The "Subscript Out of Range" error suggests that the workbook doesn't contain the specified worksheet. Or, if it exists, there might be a spelling mistake or extra space somewhere.

For the "ActiveX Component Can't Create Object" error, see if this helps...

http://www.mrexcel.com/forum/excel-...429-activex-component-cant-create-object.html

Otherwise, post your code and someone will be able to help.

 

[Apple1]

Hi

Thank you for the reply.
There is no error with the name of the worksheet as I tried the code using the order of the sheet instead, I get the error:

"ActiveX Component Can't Create Object"

With ThisWorkbook.Sheets(2)
.Cells.Copy 'Replace the sheet with values
.Range("A1").PasteSpecial Paste:=xlPasteValues
End With

The link below is pointing to this thread. Can you provide the correct link?

Thank you

The "Subscript Out of Range" error suggests that the workbook doesn't contain the specified worksheet. Or, if it exists, there might be a spelling mistake or extra space somewhere.

For the "ActiveX Component Can't Create Object" error, see if this helps...

http://www.mrexcel.com/forum/excel-...429-activex-component-cant-create-object.html

Otherwise, post your code and someone will be able to help.

 

[Domenic]

Sorry, I don't know how that happened. Anyway, here's the link...

https://msdn.microsoft.com/en-us/library/aa231060(v=vs.60).aspx