Macro, how to protect sheet, but allow pivot?

[David ...M...]

I have an event macro Workbook_Open() that contains:

For Each wSheet In Worksheets
wSheet.Protect Password:="xxxxxx", _
UserInterFaceOnly:=True, _
AllowUsingPivotTables:=True, _
AllowSorting:=True
Next wSheet

This is to protect the worksheets. I have a sheet with a summary pivot table
and have assigned a macro to a button to refresh the pivot with

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

This fails due to the protection, I assumed the UserInterFaceOnly:=True
would allow the macro to run OK. I get run-time error 1004 and a message
"That command cannot be performed while a protected sheet contains another
PivotTable report based on the same source data." "To remove protection from
the sheet that has the other report....."

But there is only one pivot table in the workbook.

Any ideas how to run the pivot refrsh macro?

Many thanks

 

[Mark H. Shin]

Try unprotecting your worksheet (using VBA) just before your refresh code.
Then protecting the sheet again after your refresh code.