CODEWITHSUNDEEP
excelpowerpivotvba
Francisco Corrêa
Francisco Corrêa

Reputation: 71

Filtering row labels in pivot table using vba

I have searched a lot of forums but still haven't found the answer for this:

I am trying to filter my row label which varies from 1 to 10, in order to only show me 5 and 10.

I wrote the following code, but it produces error 1004 "Unable to get the PivotFields property of the PivotTable class".

ActiveWorkbook.Sheets("SideCalculations-KPIs").Activate
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ToStateId")
    .ClearAllFilters
    .PivotItems("5").Visible = True
    .PivotItems("10").Visible = True
End With

thanks in advance

Upvotes: 2

Views: 15536

Answers (2)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

you could try something like this:

dim pvItem as pivotitem 
For Each pvtitem In Sheets("SideCalculations-KPIs").PivotTables("PivotTable1").PivotItems
    if (pvitem.name="5" or pvitem.name="10") then
        pvitem.visible=true
    else
        pvitem.visible=false
    end if
Next

Upvotes: 0

Vikas
Vikas

Reputation: 805

It is clearly mentioned in the error, the name of the PivotField is incorrect.

The possibility is that the Name of the Pivot Field has been changed from "ToStateId". In order to find the appropriate name, please run the following code:

For each pField in ActiveSheet.PivotTables("PivotTable1").PivotFields
     Debug.Print pField.Name
Next pField

Go to VBA Editor, press Ctrl+G, it will open the immediate window. It will show all the available pivot fields.Then please use the correct Pivot Field and try.

Upvotes: 1

Related Questions