Microsoft Excel - VBA Excel: Refer to Pivot Table without knowing the name

Asked By Rowland Hamilton on 15-Mar-13 07:07 PM

VBA Excel: Refer to Pivot Table without knowing the name

I figured it out, had to use a string for pivot table name, then realized the Field name was not consistent across documents (was a comma between tax and code).
I manually deleted the comma in the file.:

CODE:
  Revsht.Activate

    Dim PT As PivotTable
    Dim RevPT As String

    For Each PT In ActiveSheet.PivotTables
     RevPT = PT.name
     Exit For
    Next

    Revsht.PivotTables(RevPT).PivotFields("TAX CODE").ClearAllFilters
END CODE

ORIGINAL QUESTION:

How do I refer to derived Pivot table correctly:

CODE:

Revsht.Activate

    Dim PT As PivotTable
    Dim RevPT As PivotTable
    For Each PT In ActiveSheet.PivotTables
    Set RevPT = PT
    Next

'Breaks here, not properly referencing the pivot table:
    Revsht.PivotTables(RevPT).PivotFields("TAX CODE").ClearAllFilters

END CODE

Thanks Folks, Rowland

Danasegarane Arunachalam replied to Rowland Hamilton on 16-Mar-13 08:48 PM


MsgBox Worksheets("sheet3").PivotTables.Count

The above code gives how many pivot tables are there in the sheet3. And you can loop through them and get the exact sheet as you wish.

Worksheets("sheet3").PivotTables(1)

Will get the reference to the first pivot table