Results 1 to 3 of 3

Thread: Create a macro to unprotect sheet, refresh pivot table, reprotect sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    589
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Create a macro to unprotect sheet, refresh pivot table, reprotect sheet

    Make a test copy of your file & try;


    Private Sub Worksheet_Activate()

    ActiveSheet.Unprotect Password:="850"

    thisworkbook.RefreshAll

    ActiveSheet.Protect Password:="850"
    End Sub

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Jan 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a macro to unprotect sheet, refresh pivot table, reprotect sheet

    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 by trypsin25; Oct 14th, 2019 at 11:20 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •