If you've ever encountered a corrupt Excel workbook, you've probably tried lots of things already, including possibly tearing your hair out.
In this particular scenario, we have lots of extra sheets that show up in the Visual Basic Editor. This is not great.
…and it may have an extra ThisWorkbook object too!
It looks like some sheet objects have no CodeName in parenthesis, others do. Some sheets have a bunch of extra properties in the properties window!
Oh, and macros won't work anymore...
Here's what I tried:
- VBA Code Cleaner (free)
- saving as an .xlsb file
- Compiling the code to check for syntax errors (none)
- other weird stuff suggested by blog posts...
Finally, here's what worked:
- Save your macros/ Userforms, etc by exporting them or just copying the code you need into a Word/Text document.
- Save the workbook as a regular .XLSX file.
- Close the current file
- Re-open the new .XLSX file to reveal the sheets are restored to normal and the duplicates are gone!
- Now you'll need to re-add your code by importing the modules (.bas files) and Userforms OR just copy in your codes.
- FIXED!
