(Solved) – Excel VBA Protected Sheet Pivot Table Refresh

(solved)-–-excel-vba-protected-sheet-pivot-table-refresh

I am new to VBA. I created a macro that refresh the Pivot table in each of the two tabs.
The pivot tables are protected so no one can edit them. But I want these tables to be automatically refreshed each time the source has been updated. I also did update the pivot table properties so they would automatically refresh when workbook is open. But I kept getting this error every time I start the workbook.

“That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data. To remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotect Sheet, then try again.”

Could someone who have came across this error help me out? Thank you very much.

Below are my codes.

'**In Woorkbook class:**

Private Sub Workbook_Open()

Worksheets("EG_Pivot").Protect Password:="2019", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Worksheets("L4_Pivot").Protect Password:="2019", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub


'**In Worksheet class:**

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("EG_Pivot").Unprotect Password:="2019"
Worksheets("L4_Pivot").Unprotect Password:="2019"

Worksheets("EG_Pivot").PivotTables(1).PivotCache.Refresh
Worksheets("L4_Pivot").PivotTables(1).PivotCache.Refresh

Worksheets("EG_Pivot").Protect Password:="2019", AllowUsingPivotTables:=True, UserInterfaceOnly:=True
Worksheets("L4_Pivot").Protect Password:="2019", AllowUsingPivotTables:=True, UserInterfaceOnly:=True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *