Automatic Calculations on all Sheets apart from one

[ElBB23]

Hi all,

I have looked for an answer to this query for a few days but, I can't seem to find an answer that covers everything.

I have a workbook with several sheets, I want all of them to be able to calculate automatically and one to only calculate if it is clicked on. I found a solution that seemed to do this but when I clicked out of that sheet it turned all sheets to manual. I can find solutions where you have automatic calcs enabled on each sheet but, I would assume that would mean having to click on each sheet to make sure it calculates which isn't ideal if I have several sheets feeding into each other.

I apologise if this has been asked before but I cannot find it on any searches.

 

[Yongle]

This method works
- calculations are disabled in desired sheet when the workbook opens
- create a trigger (here = click a button) \ enable sheet calculation \ force a re-calc \ disable sheet calculation

To test
- add the code to your workbook
- save, close and re-open the workbook
- add formulas or change values in Sheet1 (values returned by formulas remain unchanged)
- click on button (values returned by formulas are updated)
- add formulas or change values in Sheet1 (values returned by formulas remain unchanged)

This prevents Sheet1 from calculating:

[I][COLOR=#ff0000]paste into [/COLOR][COLOR=#ff0000]ThisWorbook Module[/COLOR][/I]
Private Sub Workbook_Open()
    Sheets("Sheet1").EnableCalculation = False
End Sub

Clicking button with this code behind it forces re-calculation and then disables calcs in Sheet1

Private Sub CommandButton1_Click()
    With Sheets("Sheet1")
        .EnableCalculation = True
        .Calculate
        .EnableCalculation = False
    End With
End Sub

 

[ElBB23]

Hi and sorry for the late reply. Thank you so much works perfectly!