Pivot table refresh in a protected sheet

**JoelMessing** · 05-01-2009, 09:22 AM

Any ideas on how I can refresh a pivot table in a protected sheet? Do I have to unprotected the sheet first? If so is there any way around this, as I do not want others adjusting my formulas feeding the pivot tables.

**JBeaucaire** · 05-01-2009, 09:28 AM

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)

**JoelMessing** · 05-04-2009, 10:37 AM

Thanks for the reply. I am pretty good with excel but am a VB novice. Can you walk me though how to enter this data?

**JBeaucaire** · 05-04-2009, 11:04 AM

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.