Refresh an Excel Pivot Table on a Protected Sheet

When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables and pivot charts. However, even with that option turned on, you can’t refresh an Excel pivot table on a protected sheet. Use this macro to turn off the protection and update the pivot table.

Protect the Worksheet

You can protect a worksheet and allow users to make some changes to the pivot tables. However, there are some restrictions on the pivot table functions, when the sheet is protected. Watch this video to see how to set up the protections, and there are written details below the video.

Allow Pivot Table Use When Protecting

Here is the list of items that you’ll see when protecting a worksheet. If you want to use the pivot tables on the sheet, be sure to check the box for “Use PivotTable and PivotChart”.

Pivot Table Limitations on Protected Sheet

However, even if that box is checked, there are many limitations on what you can do with pivot tables, once the sheet is protected. Get more details on the pivot table limitations on my Contextures website.

For example, here are a couple of important limitations:

• you can’t create a new pivot table on the protected sheet
• you can’t refresh a pivot table on the worksheet

When you right-click in a pivot table cell, you’ll see that the Refresh command is disabled.

Refresh an Excel Pivot Table on a Protected Sheet

When you want to refresh an Excel pivot table on a protected sheet, you will need to temporarily unprotect the worksheet, refresh the pivot table, and then protect the sheet again.

You can do those steps manually, or use a macro, like the one below. Replace the “mypassword” string with the password for your worksheet.

Sub UnprotectRefresh()
On Error Resume Next

With Activesheet
  .Unprotect Password:="mypassword"
  .PivotTables(1).RefreshTable
  .Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
End With
    
End Sub

Refresh Pivot Table Connected to Protected Sheet

If you try to refresh a pivot table that is on an unprotected sheet, you might see a message that says: “That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data.”

I’ve moved the text in the screen shot below, so you can read it. The actual message it very wide!

That happens if the pivot table that you’re trying to refresh shares the same pivot cache as a pivot table that is on a protected sheet.

You will need to unprotect the other sheet temporarily, while refreshing any pivot tables that share the same pivot cache.

You can get a macro to unprotect all sheets and refresh the pivot tables, on my Contextures website. Both macros are also in the sample workbook that you can download on that page.

Download the Sample File

To download the sample Excel workbook with the Refresh Pivot Table macros, go to the Pivot Table Protection page on my Contextures website. The zipped file is in xlsm format, and contains macros.

______________________________