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.
Refresh pivot table on protected sheet with VBA code
Refresh pivot table on protected sheet with VBA code
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
