Create a macro to unprotect sheet, refresh pivot table, reprotect sheet

[trypsin25]

Hello! I am trying to create code that once a user click on the tab “LeadershipSoftwareOverview” the sheet will be unprotected, the pivot table on that tab (labeled “ResourceSummary”) refreshed and then the sheet reprotected. Below is what I have and it is not working. TIA!!

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="850"

Sheets("LeadershipSoftwareOverview").PivotTables("ResourceSummary").RefreshTable

ActiveSheet.Protect Password:="850"
End Sub

 

[RasGhul]

Make a test copy of your file & try;


Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="850"

thisworkbook.RefreshAll

ActiveSheet.Protect Password:="850"
End Sub

 

[trypsin25]

I got my original one to run correctly a few times then got a run-time error '1004' Unable to get the PivotTables property of the Worksheet class. Any idea what caused that?
NEVERMIND - I messed up the name of the pivot table.

Thank you for your help!!!!!