Filtering row labels in pivot table using vba

2
votes

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

2
excelpowerpivotvba
to check the name of the Pivotfield, in the Debug/Immediate window execute this code: ?Sheets("SideCalculations-KPIs").PivotTables("PivotTable1").PivotFields(1).name - it may be that the name ToStateId is wrong - Our Man in Bananas

2 Answers

1
votes

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.

0
votes

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