All PivotItems Listing as Hidden or Not Visible

[BenW71]

I am trying to go through the current pivot filters that are set up on a pivot table and store them so that they can be reapplied with a single button click. The code for all of this is in an add-in if that matters.

When i loop through the pivot fields and check the visible and not visible items, all are showing up as visible = false.

For example for the Project Type pivot field, below are the current selected values...

Debugging a bit shows that when i go through this "Project Type" field i show:

Public Sub TestGetPivotFieldItems()
Dim pf As PivotField, pi As PivotItem, pt As PivotTable


    Set pt = ActiveSheet.PivotTables(1)
    Set pf = pt.PivotFields("Project Type")
    Set pi = pf.PivotItems("EDF")
  
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " -> " & pf.Name & " -> " & pi.Name & ".Visible = " & pi.Visible)
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.PivotItems.Count & " total items")
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.VisibleItems.Count & " visible item which is " & pf.VisibleItems(1).Name)
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.HiddenItems.Count & " not visible items")
  
End Sub

Gives the result:
Full Portfolio -> Project Type -> Project Type -> EDF.Visible = False
Full Portfolio -> Project Type has 10 total items
Full Portfolio -> Project Type has 1 visible item which is (All)
Full Portfolio -> Project Type has 10 not visible items

However, in the screenshot you can see that EDF is checked, as are most of the others... I don't know why it doesn't show 2 as not hidden and the other 8 as visible. i can deal with (All) in the code.

 

[RoryA]

Seems like a bug to me. Not sure how you'd work round it though.

 

[BenW71]

Seems like a bug to me. Not sure how you'd work round it though.

Thanks for trying. if i find a workaround (other than telling anyone who wants to save a pivot to manually touch each filter), i'll post here.

 

[BenW71]

Seems like a bug to me. Not sure how you'd work round it though.

Rory, i found this post... Excel VBA loop through pivotitems that are filtered

I don't really know what a PivotLine is or pivot axis. But could this approach assist?

 

[RoryA]

It won't work for a page/filter field unfortunately.