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

trypsin25

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

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RasGhul

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

Watch MrExcel Video

Forum statistics

Threads
1,090,142
Messages
5,412,696
Members
403,442
Latest member
keowinvip1

This Week's Hot Topics

Top