Refresh Pivot Table Macro (Excel 2002)

Topic

New Reply

Hello,

The following macro refreshes the named pivot table when I go into the active worksheet:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(“PivotTable1”).RefreshTable
End Sub

I now have multiple pivot tables on the active worksheet… how can I tweak the code to refresh ALL the pivot tables on this active worksheet. I tried the following, but it didn’t like it…

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(“PivotTable1”).RefreshTable
ActiveSheet.PivotTables(“PivotTable2”).RefreshTable
End Sub

Likewise, I tried having TWO macros, naming the 1st table in the 1st macro and the 2nd in the 2nd macro… it didn’t like that either.

I thought I could handle this one, but was obviously wrong…

As usual, I appreciate any suggestions!!

Thank you!
LJM

Reply | Quote

Replies

Try this:

Private Sub Worksheet_Activate()
Dim pvt As PivotTable
For Each pvt In Me.PivotTables
pvt.RefreshTable
Next pvt
End Sub

By using For Each, you don’t depend on the exact number of pivot tables or on their names. The code will loop through all pivot tables on the worksheet. In the worksheet module, Me refers to the worksheet.

Reply | Quote

Thanks for the code and thanks for the explanation… this helps me learn!! As you’d expect, it works great!
LJM

Reply | Quote