Ошибка 40036 vba

i have an issues with an macro that i’m writing. I have my xlsm file that i’m writing my macro in, it’s creating a template that gets data from multiple spreadsheets that other people send me. However one file that i use for information is an xlsm file and is giving me problems. I believe i have isolated the problem but am unsure as to how to fix it.

the first sheet is named «Piranha» (no idea why), and the second sheet is named «Piranha — Dist & Growth split»

however when i write stardard code like
Sheets(«Piranha»).select

it has a runtime error 40036 (Application- defined or object defined error)

it also doesn’t like
ActiveSheet.select / .name

but only for those two sheets, there is another sheet named «Exceptions» and i can use that object no problem. Has anyone ever heard of this problem or better have any ideas on how to solve it? Also because it’s not my file if there is a way to do it without actually altering the file would be amazing. Thank you!

answered Jul 14, 2014 at 0:09

answered Jul 11, 2014 at 5:51

Sub test()
Sheets("Sheet1 & 2").Select
ActiveSheet.Select
End Sub

Sub test2()
Sheets("Sheet1").Select
ActiveSheet.Select
End Sub

Tested your issue with the above code and I was unable to replicate your fault. Perhaps there is a space at the beginning or end of one of the sheets? As for ActiveSheet not working, it works OK in my test. I think you need to check your code, or at least update your Question with your code so we can check it

answered Jul 11, 2014 at 6:59

All these answers talk about compiling and clearing errors. If you’re like me, you do that all the time. The answer didn’t seem clear to me so here it goes:

Not only does your macro workbook need to be free of errors, so do ALL other workbooks you open. So if you’re digging through a ton of data and you hit these bizarre object errors then it’s possible there is a workbook with compile errors in your dataset. No amount of debugging your main workbook will fix the problem.

You have to be able to successfully compile every workbook you open programmatically. The OP said something about stripping «the vb components» out of his data. Any method like this you can do that avoids macros in your target data workbooks will work-around this issue. You can save each file as macro-free XLSX and then pull data from that. Or you could use ADO to access the data in a query.

answered Jul 26, 2021 at 19:49

I have the same problem and solved it by first finding which sheet was being accessed at the time of the error.

I copied all the data from that sheet (including formulas, formatting) and pasted into a new sheet along wit the sheet’s VBA code. I deleted the old sheet and renamed the new set back to the old sheet.

On other words, I recreated everything back to what it was.

The error disappeared.

answered Nov 13, 2022 at 18:46