Excel with 100+ sheets keeps crashing after cca 10 clicks at some random cells.

[hodikr]

Im using excel with 100+ sheets. There is one sheet as a database of inputs (values in 500 rows & 20 columns). And 100+ sheets, where each sheet is a specific calculation using data from input sheet. Data are used via VLOOKUP.
It works very well, until some point, where I cannot add more sheets … at about 140 sheets. Not sure if I reached kind of a limit. Or when I keep less sheets, about 130, it works fine, but after couple of clicks and sheet selection changes, it crashes too. And no idea why. It never happens when working with 50 sheets only.
Not sure if there is kind of a limit for sheets. But 130 seems crazy low to me. The file has just 7MB.
I have older version of the file with 250 sheets, and it works just fine. The newer file, which crashes, has just improved visualizations and some formulas are changed, but its basically same thing.
I would appreciate any help. We could do online session via MS Teams.
Thank you.

EDIT: Added attachment. Use the "TestAddSheets()" macro to add new sheets.

[hodikr]

Does anybody have any idea, why is this happening and how to avoid that ?

I just tried adding new sheets via macro and checking time to add new sheet via timer. Something strange happens after about 140 sheets. Macro runs till the end, but excel does not respond then.

Attachment 699554

[hodikr]

Attachment added to test the file. Use TestAddSheets() to add new sheets. I would like to understand, why does the excel freeze when adding more than 140 sheets. The file size is about 7MB with 140 sheets.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

As you are new and probably forgot to read the forum rules I will add it for you

https://www.mrexcel.com/board/thread...ezing.1148369/

[Pepe Le Mokko]

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day . Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[hodikr]

Thanks for the hint Pepe. It was not mine intention to push. I was just trying mine luck on 2 sites. I apologize if I broke one of the rules.

I understand that people help voluntarily here.

[Pepe Le Mokko]

Posting in multiple forums is not prohibited as long as you add links ( or indicate where you have cross posted in your post) to the other posts. It's usually not a very good idea as you will have read in the link provided