Disable Sheet Calculation

[ochiha]

Dear All,
I have Ms Excel 2010, and got a huge sheet with many, "Vlookup", "Sumifs", "Sumproduct", "Conditional Formating"... etc formulas...
the good thing is that those formulas are divided into separate sheets.

How can I disable the sheets that i do not wish to have them updated all the time and update them whenever I need them.

1. if possible can you experts explain this step by step
2. It would be wonderful if there can be two methods to do this such as, Macros, and regular disable and enable Commands...

regards,

 

[Trevor G]

Two options.

First use the File tab and then options in the Options look at the Formulas and then you can cancel the Automatic Calculate and use it as manual, which can be run from Pressing F9.

Second method is use the worksheet select/activate method. So you would select the worksheet name (Tab) use right mouse button and View code look at the options at the top and on the right drop down (Shows SelectionChange) change this to Worksheet_Activate

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub

Also when you want to run this from a macro

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> calc()<br>    <SPAN style="color:#00007F">Dim</SPAN> MyArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsht <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Set</SPAN> MyArray = Sheets(Array("Sheet1", "Sheet3"))<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsht <SPAN style="color:#00007F">In</SPAN> MyArray<br>        <SPAN style="color:#00007F">With</SPAN> wsht<br>            Application.Calculation = xlCalculationManual<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wsht<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ochiha]

You have been great

thank you