VBA code to enable-diable calculations for hidden sheets

[nhayden73]

Hello,

I am working on a large workbook with 32 tabs (roughly 1M file). About 22 of the tabs are not used on a regular basis but they all contain a number of calculations, references and Index/Match with the primary sheets. All of this being active causes the workbook to slow down. I am looking for some VBA code that would enable/disable calculations on sheets based on their active/hidden state.

Any suggestions would be greatly appreciated.

Also all of these sheets are currently running a VBA code that renames the sheet tab based on cell values so I want to make sure that the added code does not effect this when active.

Thank you in advance for any assistance.

 

[steve the fish]

Turning off calculations affects the whole workbook

 

[nhayden73]

What about switching the calculations between manual and automatic based on the sheet being active or hidden?

 

[steve the fish]

Well you could do that. I don't think I ever have calculations turned off except for when running macros. It can cause errors if you don't realise. Its all too easy to forget the workbook isn't calculating.

 

[shg]

See Help for Worksheet.EnableCalculation Property