Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Refresh Pivot Table Macro (Excel 2002)

    Hello,

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

    Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    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").RefreshTabl e
    ActiveSheet.PivotTables("PivotTable2").RefreshTabl e
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh Pivot Table Macro (Excel 2002)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Refresh Pivot Table Macro (Excel 2002)

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

Posting Permissions

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