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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

(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.