My Pivot Table won’t Refresh!

How to Solve Problems in Protected Workbooks

By Jeff Robson

You may come across some interesting behaviour (aka a real “gotcha”) when working with Pivot Tables and VBA Macros in protected workbooks and sheets.

When working on a file where each sheet has been protected using the userinterfaceonly:=true flag, you may find that even though you run a macro to unprotect the sheet containing the pivot table, your macro still crashes with a 1004 runtime error, saying it can’t refresh the pivot table because the sheet is protected.

Very frustrating!

The reason for this may be that elsewhere in the file there is a Pivot Chart, sitting on a protected chart sheet that has been based on the pivot table you’re trying to refresh.

If this is the case, the pivot table can’t refresh unless you unprotect the chart sheet as well. This problem exists because chart sheets can’t be protected with userinterfaceonly:=true as this applies to worksheets only.

That may save you some frustrated pulling out of hair!