Refresh pivot table and report in protected sheets

deodion

New Member
Joined
Dec 9, 2008
Messages
8
I have sheet-a, it contains pivots only, only allows select locked/unlocked/autofilter,
and sheet-b, it contains pivot chart, only allows select locked/unlocked/use pivot table & report,

sheet-c contains both pivot and report, only allows select locked/unlocked/use pivot table & report,

some other sheets with table only, only allows select locked/unlocked/autofilter,

they are all protected sheets,

what is the efficient way to refresh protected sheets which contain pivot table and/or pivot report?

the goal is to let user only input selected cells in other sheets,
while sheet-abc, they only can use pivot table & report and/or autofilter,

I have below from other source, but I dont want to make all sheets AllowUsingPivotTables:=True
Perhaps, I only need the adjusted code where it can simply unprotect/protect each desired sheet

I dont know any VBA,

VBA Code:
Sub UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
    ws.Unprotect Password:="mypassword"
Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
  ws.Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
Next ws
    
End Sub

thank you,
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,118,807
Messages
5,574,425
Members
412,591
Latest member
dawnkotzebue
Top