Need Help Creating Recalculate all Sheets Subroutine

[ham123]

Greetings experts,

I need help creating a subroutine which is identical to this command:
CTRL + ALT + SHIFT + F9 to recheck all formula dependencies and then recalculate all formulas.

I need it to be able to run for all tabs(I could have up to hundreds of tabs)

Any help is much appreciated

 

[GraH - Guido]

Might be

Application.CalculateFullRebuild

You could have recorded a macro in which you do the manipulation with the key strokes by the way .

 

[ham123]

Hi, thank you for your reply!
I did try that and made it into a command button but it didn't really work
Can you help me put it in here?

Private Sub CommandButton1_Click()

Call Button1_Click

End Sub

Sub Button1_Click()
    Dim WS As Worksheet
    
    Application.ScreenUpdating = False
    For Each WS In ThisWorkbook.Worksheets
        Call CopyPasteValuesInTab(WS.Name)
    Next WS
    
    ActiveSheet.Calculate
    Application.ScreenUpdating = True
    
    MsgBox "Done!"

End Sub

Sub CopyPasteValuesInTab(ByVal SheetName As String)
    On Error GoTo EH
    Dim WS As Worksheet
    Set WS = Sheets(SheetName)
    WS.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Exit Sub
EH:
    Debug.Print Err.Number & ": " & Err.Description
    Application.CutCopyMode = False
End Sub

 

[GraH - Guido]

Hi, in your code you only recalculate the active sheet

 ActiveSheet.Calculate

Replace that with the code provided and retry.

 

[ham123]

Thank you so much for your help! Below is the complete code for your reference.

I managed to do it after adding in these:

    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild

The Full Code:

Private Sub CommandButton1_Click()

Call Button1_Click

End Sub

Sub Button1_Click()
    Dim WS As Worksheet
    
    Application.ScreenUpdating = False
    For Each WS In ThisWorkbook.Worksheets
        Call CopyPasteValuesInTab(WS.Name)
    Next WS
    
    ActiveWorkbook.RefreshAll
    ActiveSheet.Calculate
        
    Application.CalculateFullRebuild
    Application.ScreenUpdating = True
    
    MsgBox "Done!"

End Sub

Sub CopyPasteValuesInTab(ByVal SheetName As String)
    On Error GoTo EH
    Dim WS As Worksheet
    Set WS = Sheets(SheetName)
    WS.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Exit Sub
EH:
    Debug.Print Err.Number & ": " & Err.Description
    Application.CutCopyMode = False

 

[GraH - Guido]

Refresh all? So you'd have data connections/pivots?
Also you use a for each WS - I missed that before. Something is strange in there - before refreshing/calculating you are pasting values. So what formulae do need to be recalculated then if all is already replaced by values?