Use of Pivot Table in a Protected sheet

**lclarke** · 12-19-2012, 07:59 AM

Hi,

I know the use of pivot tables in protected sheets has been discussed a lot, but they always seem to revert to using Macros to unprotect, refresh, then reprotect the sheet.
This is not feasible for me as the people who will be using the file cannot be trusted to run the macro on the correct sheet (i.e. not impact on other sheet).
Additionally, i cannot store the pivot table on a different (unprotected) sheet due to the sheer number of projects that will come on line / close over the lifetime of the file.

Basically, exluding the use of Macros or background sheets. Is there any way to refresh a Pivot table on a sheet that is protected?
(i've tried keeping cells in the area of the pivot table unlocked)

Louise

**dilipandey** · 12-19-2012, 08:36 AM

Hi Louise,

Welcome to the forum
While protecting, check the checkbox " use pivot table reports" ... thanks.
Also click the options which enables data refresh on open

Regards,
DILIPandey

<click on below 'star' if this helps>

**JosephP** · 12-19-2012, 08:44 AM

no there isn't. a well-written macro would not allow the possibility of the wrong sheet being unprotected. also you can protect the sheets in code using the userinterfaceonly:=true argument and then your code can refresh the tables without unprotecting the sheet