How to refresh pivot table on protected sheet?
Normally, the pivot table is not refreshed on a protected sheet, this article, I will introduce a VBA code to refresh the pivot table from a protected worksheet.
By default, there is no direct way for you to refresh pivot table on a protected sheet, but, with the following VBA code, you can quickly refresh all pivot tables of current worksheet when data changes in your original table.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Refresh pivot table on protected sheet:
Sub RefreshAll() 'Updateby Extendoffice 20161109 Dim xpt As PivotTable With ActiveSheet .Protect UserInterfaceOnly:=True For Each xpt In .PivotTables xpt.RefreshTable Next xpt End With End Sub
3. And then press F5 key to run this code, if your original data changes, all pivot tables in the active sheet will be refreshed at once.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agothanks a lot
- To post as a guest, your comment is unpublished.· 4 years agoHello,
I successfully setup this Macro within my workbook. The only snag I've hit is when I have an additional unlocked pivot in the workbook (on a different tab) and I run the Macro within the unlocked pivot tab, an Error occurs. It's an easy fix by deleting the non-locked pivot tab. It also doesn't result in an error if I run the Macro in the locked tab. However, I'm wondering if there is a way to enable Macro to work across Locked and Unlocked pivots?