Refresh All Pivot Tables

**smyuen** · 12-26-2012, 01:05 AM

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.

**JBeaucaire** · 12-26-2012, 02:03 PM

That macro would go into the ThisWorkbook module.

**smyuen** · 12-26-2012, 02:16 PM

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.

**smyuen** · 12-26-2012, 02:25 PM

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

**smyuen** · 12-28-2012, 12:00 PM

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!

**JBeaucaire** · 12-28-2012, 10:05 PM

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.