Recalculate all but one sheet (URGENT)

[skutos]

Hi,

Is there a way to keep autocalculation on so it recalcs every few minutes but does not recalculate one specific sheet?

I know I can write some VBA code to turn on manual calculation and then loop through the different sheets while leaving out the one I want but then I would have to press the button everytime i want the book to recalculate which is not what I want. I want that my book gets recalculated every few minutes automatically while one of my sheets requires a press of a button in order to let it recalculate.

Any help would be highly appreciated!

ps. My workbook is called "Trade Volume Final_incl PSHYPO TEST" while the worksheets I dont want to update are called "Output Static" and "Output variable"

Thanks!

 

[tweedle]

Something like this...

Adjust Array as needed.

Sub ButtonPushPauseCalcs()
Dim NoCalc
NoCalc = Array(Sheets("Output Static"), Sheets("Output variable"))
Debug.Print UBound(NoCalc)
For ShtIdx = 1 To Sheets.Count
    For ArrIdx = LBound(NoCalc) To UBound(NoCalc)
        If Sheets(ShtIdx).Name = NoCalc(ArrIdx).Name Then
            Sheets(ShtIdx).EnableCalculation = False
        End If
    Next ArrIdx
    
Next ShtIdx
End Sub

 
 
Sub ButtonPushToCalc()
Dim ForceCalc
ForceCalc = Array(Sheets("Output Static"), Sheets("Output variable"))
Debug.Print UBound(ForceCalc)
For ShtIdx = 1 To Sheets.Count
    For ArrIdx = LBound(ForceCalc) To UBound(ForceCalc)
        If Sheets(ShtIdx).Name = ForceCalc(ArrIdx).Name Then
            Sheets(ShtIdx).EnableCalculation = True
        End If
    Next ArrIdx
Next ShtIdx
Application.CalculateFull
End Sub

 

[Derek Brown]

Removed - already answered.

 

[skutos]

Thanks a lot!

So can I just put the first macro into "thisworksheet" so that it doesn't calculate every sheet when I open the worksheet for the first time? The biggest problem is that it takes too long to open up the file when it calculates those two sheets.. Would I have to make some kind of adjustment to do so?

 

[tweedle]

Would be best to put them in their own Module and call them as needed.

Some Options for you... depending on what you ultimately want to happen

'With a button on sheet Output Variable
Private Sub CommandButton1_Click()
    'Do do the calc and turn it back off
    ButtonPushToCalc
    ButtonPushPauseCalcs
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Would pause the calcs on workbook close
    ButtonPushPauseCalcs
End Sub
 
Private Sub Workbook_Open()
    'Would pause the calcs on workbook open
    ButtonPushPauseCalcs
End Sub

(see part 3 of sig.)