Further to the above, I saw the following VBA code by Debra Dalgleish that supposedly unprotects the Pivot table, refreshes it, and protects it again (I said "supposedly" because I have not tried it myself). I'm wondering if there is a way to combine your code and hers in some fashion:

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 Sub


I suppose this code would have to be placed in "This workbook?"