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

trypsin25

New Member
Joined
Jan 7, 2016
Messages
10
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

Well-known Member
Joined
Jul 15, 2016
Messages
606
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

New Member
Joined
Jan 7, 2016
Messages
10
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!!!!!
 
Last edited:

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top