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