Turn off the AutoCalculation in one sheet only VBA

[PaulaGon]

Hello,


I have a large workbook with a lot of formulas and every time we change or insert data, by default, excel recalculates all the formulas and it takes a long time,. In my workbook I have Sheet1 that I use to insert daily information in Sheet2 I have almost all the formulas. I need to create a macro that disable the automatic calculation only in Sheet2. Is that possible? Calculation mode set to manual only in one sheet and create a comand button to calculate these sheet only when is need?

Thank in advanced!

 

[shg]

Re: Turn off the AutiCalculation in one sheet only VBA

worksheets("sheet2").enablecalculation = false

 

[PaulaGon]

Re: Turn off the AutiCalculation in one sheet only VBA

I am sorry for this question...but i am a not very good with macros...

Where I put the code? In the developer at the level of the sheet?

Thank you

 

[shg]

Sub TurnOff()
  Worksheets("sheet2").EnableCalculation = False
End Sub

Sub TurnOn()
  Worksheets("sheet2").EnableCalculation = True
End Sub

1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

Use Alt+F8 to run one macro or the other.

You'll need to save the workbook as an xlsm file.

 

[PaulaGon]

This is perfect! Thank you very much for the help, sgh!!

 

[shg]

You're welcome.

It would probably be worthwhile to eliminate volatile formulas on Sheet2, then that probably wouldn't be necessary.

 

[canefan17]

Sub TurnOff()
  Worksheets("sheet2").EnableCalculation = False
End Sub

Sub TurnOn()
  Worksheets("sheet2").EnableCalculation = True
End Sub

1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

Use Alt+F8 to run one macro or the other.

You'll need to save the workbook as an xlsm file.

I tried this and it did disable auto-calculate, however, when I typed in a vlookup formula and a sum formula (to test to auto-calculate) it returned only 0s.

Then I turned off the macro and magically the correct values appeared.