Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

KG wrote:
I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub


It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html