Pivot table refresh in a protected sheet

A couple of methods I've seen and one I've used. Most importantly is setting the "UserInterfaceOnly:=True" flag for protection so that macros are free to effect your sheets while users are not.

Right-click on the Sheet tab that has your pivot table on it and select VIEW CODE, paste in this code:

Private Sub Worksheet_Activate() 
    Dim pt As PivotTable 
     
    Me.Protect Password:="Secret", UserInterfaceOnly:=True 
     
    For Each pt In Me.PivotTables 
        pt.RefreshTable 
    Next pt 
End Sub

If you have pivottables on multiple sheets, then paste this code into the THISWORKBOOK module instead:

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
    Dim pt As  PivotTable, bPiv As Boolean 
     
    If Sh.Type <> xlWorksheet Then Exit Sub 
     
    For Each pt In Me.PivotTables 
        Me.Protect  Password:="Secret", UserInterfaceOnly:=True 
        pt.RefreshTable 
    Next pt 
     
End Sub

(source material)

These are WORKSHEET macros, they go in the worksheet module itself.

Right-click on the sheet tab and select VIEW CODE.
The VBEditor will open and the SHEET MODULE will already be open. Paste in the code. Be sure to correct the "password" in the code to your password, or remove that portion (leave in the comma).

Press Alt-Q to close the editor and save your sheet. Now anytime you select that sheet, the pivottables on that sheet will all refresh.

You could experiment with putting that code in a Worksheet_Calculate() event instead.