Excel Pivot Filter PivotItems Listing at times as Hidden or Not Visible in VBA

[benweb]

My basic requirement i am trying to solve is that i want to be able to store all the selections IN THE FILTER OF a pivot table and save them, letting the user reapply them as "stored filters". All of the code (including a hidden worksheet with the filters pivot field / value is part of an excel macro (XLAM) file. That XLAM has a macro that:

• Runs through each pivot field in the pivot table that is a page or row field
• If it has any NOT visible items (ie if it is being used as a filter), i will store the visible items in the hidden workbook with pivot field name / pivot item name pairs

Then i will be able to reapply the filter.

HOWEVER, at some times when you access the filter and go through pivotitems in a field ALL of the pivot items are showing with .visible = false, even though the pivot is clearly filtered and does show values.

For example, with the Product Type pivot field, here were the currently selected values:

Debugging a bit shows that when i go through this "Project Type" field i get (this is a sample debug function to easily show the problem i'm having):

Public Sub ProjectTypePivotTest()
Dim pf As PivotField, pi As PivotItem
    Set pf = ActiveSheet.PivotTables(1).PivotFields("Project Type")
    For Each pi In pf.PivotItems
        Debug.Print "'" & pi.Name & "' Visible:  " & pi.Visible
    Next pi
End Sub

This gives the following output (ALL FALSE even though most should be TRUE):

'Business' Visible: False
'Business Support Project (BSR ‘s)' Visible: False
'Core Support Project' Visible: False
'XYZ agile' Visible: False
'XYZ Scrum' Visible: False
'XYZ XP' Visible: False
'Discovery' Visible: False
'EDF' Visible: False
'Out of Office' Visible: False
'Production Support Project' Visible: False

I've also noticed that if i try to change the .visible property in the code from true to false and then back, the line does not error but it stays the same false value before and after do that change

For example:

        Debug.Print "'" & pi.Name & "' Initial Visible:  " & pi.Visible
        pi.Visible = Not pi.Visible
        Debug.Print "'" & pi.Name & "' Changed Visible:  " & pi.Visible
        pi.Visible = Not pi.Visible
        Debug.Print "'" & pi.Name & "' Final Visible:  " & pi.Visible

gives the result  (with my comments in all caps)

'Business' Initial Visible: False    CORRECT - SHOULD HAVE BEEN FALSE
'Business' Changed Visible: False   SHOULD HAVE CHANGED TO TRUE
'Business' Final Visible: False      CORRECT
'Business Support Project (BSR ‘s)' Initial Visible: False     SHOULD HAVE BEEN TRUE
'Business Support Project (BSR ‘s)' Changed Visible: False   SHOULD HAVE CHANGED TO FALSE
'Business Support Project (BSR ‘s)' Final Visible: False    SHOULD HAVE BEEN TRUE

It seems that the value is just always false regardless of what it should be, and can't be changed 

HOWEVER, If i then MANUALLY change the second pivot item in the list to be UNCHECKED (the BSR line) and rerun that same macro I get the correct results...

'Business' Visible: False
'Business Support Project (BSR ‘s)' Visible: False
'Core Support Project' Visible: True
'XYZ agile' Visible: True
'XYZ Scrum' Visible: True
'XYZ XP' Visible: True
'Discovery' Visible: True
'EDF' Visible: True
'Out of Office' Visible: False
'Production Support Project' Visible: True

Can anyone theorize why I am getting the wrong values at times instead of what is actually selected?

Additional background if it helps debug...

• The code is in an XLAM file, not the workbook with the pivot table
• The workbook with the pivot table does NOT have the baseline data in it any more, it is generated from a HUGE set of baseline data and published with the pivot tables
• The .visibleitems property of pivotfield is also not similarly