Sheet in VBAProject gets copied and converted into ThisWorkbook

[Jeffrey Mahoney]

I'm having a problem with one particular workbook. I have a worksheet with a table that is 916 columns by 126 rows. In the VBA project I had changed the Code name to CostDetails.

Somehow, the sheet got copied twice only in VBAproject. The Code Names for the two copies are CostDetails and CostDetails1 and then the real sheet code name got changed to CostDetails11. All the sheet code moved into CostDetails1. When I click the two copies, the properties window shows the name for both as ThisWorkbook.

This happened once before on this workbook. I spent a huge amount of time making a duplicate from scratch by copying the values and adding the cell formats & formulas manually.

In the snapshot below, you can see that I changed the code name of the real sheet to CostDetSht to temporarily solve the problem.

Does anyone know of a way to fix this and prevent this?

UPDATE: It seems to keep making copies if I change the code name of the REAL sheet

 

[sijpie]

That is weird! As you can see from the icons, it indeed looks like they are aliases of ThisWorkbook. How that is possible??
Perhaps a private message to one of the MVPs on the forum? They might understand the structure of a workbook better

 

[Jeffrey Mahoney]

Update: I've tried so many things so I'm not sure if this one process solved it or if it's because I tried several things.

I saved the file as .XLSX (this removed my vba code and my custom UI for the ribbon menu)
Closed and Reopened
Deleted all my named ranges including references to external workbooks (excluding table references)
Deleted all my Conditional formatting
Saved, closed and reopened
Copied all my code back into standard modules, sheet modules, and the workbook module
Added my custom UI ribbon menu back in
Recreated all my conditional formatting

It's been working for about 10 ten days without creating psuedo versions of Thisworkbook.

 

[Jeffrey Mahoney]

This continues to happen even though I changed my "Excel Tables" to regular ranges. I split up my really large table to two smaller ones. Each of those tables are over 500 columns and 340 rows. Now I have 5 different "ThisWorkbook" objects.

Does anybody have a reason why this happens?

In my VBA code I use the CodeName of the sheets because the regular sheet names may change. When this problem happens, it changes the CodeName each time and it also removes the code in those sheets and moves them to the new funky sheets.

 

[Jeffrey Mahoney]

I came across this, but it didn't resolve the issue for me
Redirecting

The only way to fix this as described above is to save as .XLSX, Save again as .XLSM, then copy all the VBA code into the proper places from the original file.

 

[Jeffrey Mahoney]

Every time I cycle a Save, Close, and reopen, the number of copies of my 2 sheets gets larger and renamed. I have no structured Excel tables on them anymore, so that can't be the cause. Each sheet has about 43,000 formula cells.

 

[MARK858]

See if any of the suggestions in the link below help

Excel VBA Project has generated multiple Workbook objects

I am responsible for a very large Excel 2010 spreadsheet with links to all sorts of external data sources including Bloomberg, 65 worksheets with vba modules and references to other vba add-ins. I...

stackoverflow.com

 

[Jeffrey Mahoney]

Thanks for that Link Mark. I've seen similar things. Based on one of the fixes, I think I'll copy the "xl/vbaProject.bin" from a good working version of this WB and save it for later.

I have been able to temporarily fix the issue, but I'd like to know how to prevent it from happening in the first place. One of the people in the thread said they were sending a sheet object to a standard module SUB, and THAT what was causing the issue. I don't have any calls like that.

What have I learned:
To fix the issue: Save as .XLSX and close. Open .XLSX, copy all the VBA code from the old (.XLSM), save as new .XLSM. All custom UI Ribbon information persists in .XLSX.
I am monitoring a way to prevent this: After saving as .XLSB I have yet to see any new extra Sheets as Thisworkbook objects. I will keep you posted

Does anybody know of any drawbacks to using .XLSB vs .XLSM?

 

[MARK858]

Does anybody know of any drawbacks to using .XLSB vs .XLSM?

Only really it's compatibility with other programs as it isn't an XLM file type