Auto refresh of Pivot Table

[K. Georgiadis]

I found the following code on C. Pearson's website:

Sub Autpen()
Application.OnSheetActivate = "UpdateIt"
End Sub

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

However, I cannot make it to work either within a
separate module or within "This workbook." Does it
perhaps need to be specifically declared as a Public Sub?

 

[icestationzbra]

i got the following macro from debra's webiste (contextures.com). i hav
tried it and it works for me.

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="MySecretWord"
Next

End Su

 

[K. Georgiadis]

Do you remember in which section of Debra's website you
found this? It seems that this code unprotects the
worksheet, refreshes it and then resets the protection.
Is that how it works?