Pivot tables wont work in protected sheet?

[Roger on Excel]

I use pivot tables in my spreadsheet.

When I protect the sheets to prevent changes to locked cells, the pivot
tables wont allow data refresh?!

Even when I check all the boxes when protecting a sheet - including the
pivot tables check box, still the pivot tables cannot be activated.

The pivot table cells are all unlocked as is the data it reads from.

Can anyone help me?

Tahnks,

Roger

 

[Debra Dalgleish]

You can't refresh the pivot table if the sheet is protected, no matter
what protection settings you used.
In the code you can unprotect the sheet, refresh the pivot table, then
protect the sheet.

 

[Roger on Excel]

Thanks Debra,

Can you suggest code for me to use on a protected sheet - i can assign it as
a macro to refresh the pivot table with a button by the pivot table?

Best regards,

Roger

 

[Debra Dalgleish]

Sub RefreshPivot()
With ActiveSheet
.Unprotect Password:="MyPwd"
.PivotTables(1).RefreshTable
.Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowUsingPivotTables:=True
End With
End Sub