Refresh Pivot Table on Protected Sheet

When you protect a worksheet in Excel, you’ll see a list of items that you can allow people to use, while the sheet is protected.

One of those options is ‘Use PivotTable reports’, and if you add a check mark to that option, things might not work exactly as you expected.

Limited Use of Pivot Tables

When the sheet is protected, even if you have allowed pivot table use, you can’t create a new pivot table. Another restriction is that you won’t be able to refresh a pivot table on the worksheet. If you right-click in a pivot table cell, the Refresh command is disabled.

Refresh on Protected Sheet

If you need to refresh a pivot table on a protected sheet, you could

• manually unprotect the worksheet
• refresh the pivot table
• protect the sheet again

Another option, if you would prefer to refresh the pivot table programmatically, is to create a macro that does these steps for you. The following macro will refresh the first pivot table on the active sheet, which has a password – “mypassword”

Sub UnprotectRefresh()
On Error Resume Next

With Activesheet
  .Unprotect Password:="mypassword"
  .PivotTables(1).RefreshTable
  .Protect Password:="mypassword"
End With

End Sub

Connected Pivot Tables

If other pivot tables are connected to the same pivot cache are on protected sheets, you won’t be able to refresh a pivot table, even if it is on an unprotected sheet.

In this situation you will have to unprotect the sheet(s) where those other pivot tables are located, and then do the refresh.

There is sample code to unprotect all the sheets, and do a refresh all, on my Contextures website: Refresh Pivot Table Connected to Protected Sheet

_______________________