+ Reply to Thread
Results 1 to 6 of 6

Refresh All Pivot Tables

Hybrid View

  1. #1
    Registered User
    Join Date
    12-25-2012
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    4

    Refresh All Pivot Tables

    I have pivot tables on multiple sheets but when I tried pasting the code provided in this thread, i get an error and the first line of the code "Private Sub Workbook_SheetActivate(ByVal Sh As Object)" is highlighted in yellow by the debugger.

    Please help?

    Thanks.
    Last edited by JBeaucaire; 12-28-2012 at 10:02 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pivot table refresh in a protected sheet

    That macro would go into the ThisWorkbook module.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    12-25-2012
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot table refresh in a protected sheet

    Hi JBeaucaire,
    Thanks for the quick response.

    I did place the code in the "ThisWorkbook" module. I also updated the password in quotes to be what I have created for each worksheet (same password for each worksheet). Could the problem be that not every worksheet contains a pivot table?

    I also tried placing the code you have on each individual worksheet but that did not work for me either. I get the error message: "run-time error '1004': Refresh Table method of Pivot Table class failed."

    I am really ignorant on macros and vba. Any help is much appreciated.

  4. #4
    Registered User
    Join Date
    12-25-2012
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot table refresh in a protected sheet

    Hi JBeaucaire,
    The error message when I place the code (shown below) in the "ThisWorkbook" module is:
    "Compile error: Method or data member not found".

    Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
        Dim pt As  PivotTable, bPiv As Boolean 
         
        If Sh.Type <> xlWorksheet Then Exit Sub 
         
        For Each pt In Me.PivotTables 
            Me.Protect  Password:="Secret", UserInterfaceOnly:=True 
            pt.RefreshTable 
        Next pt 
         
    End Sub
    Last edited by JBeaucaire; 12-28-2012 at 10:02 PM. Reason: Added code tags, as per forum rules. Don't forget!

  5. #5
    Registered User
    Join Date
    12-25-2012
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot table refresh in a protected sheet

    Hi JBeaucaire,
    My file contains pivot tables in many worksheets but some are tied to the same source data so as not to make the file huge. I believe that all the related worksheets need to be unprotected before they can be refreshed. Could this be causing the error?

    Thanks much!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Refresh All Pivot Tables

    As per forum rules, I have added code tags to your post #4. Since this query seems to have gone beyond the simple "where do i put this" and become something more, also per forum rules I have moved your questions to a thread of its own.

    Please take a moment to read all the forum rules as you are expected to abide them.

    The code given previously works as shown. I guess I cannot suggest what might be wrong with your workbook without seeing it. Click GO ADVANCED and use the paperclip icon to post up a copy of your troubling workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1