I have a PageField on one of my pivot tables that needs to include both 9999 and 99999 using Multiple Items (drag it down to a column field, hide all elements but 9999 and 99999, then drag up to the page field and select (All) will yield (Multiple Items) instead and will have just 9999 and 99999 selected). My problem is coding this. When a new element is added to the Pivot table, say 55, by default it is visible, so now my pivot table will include 9999 and 99999. My solution to this is:
The problem is that this is SLOW, for each of the 1000 items in the pivotfield, it recalculates the pivottable (even if calculation is set to manual), even if its Visible status did not change. There should be a much faster solution since really, I only want to hide 55 (or whatever other new fields may have slipped in). So I wrote:
The problem here is that PVTITM.Visible is ALWAYS FALSE. This apparently is true for any Page Field. I could write
and will get nothing but FALSE. Why is it always false? and how can I tell if it is really false? (meaning that it doesn't show up in the (Multiple Items) list)
Thanks,
~Gold Fish
Code:
Sub CheckMultItems()
Dim PVTITM As PivotItem
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
If PVTITM.Name = "9999" Or PVTITM.Name = "99999" Then
PVTITM.Visible = True
Else
PVTITM.Visible = False
End If
Next PVTITM
End Sub
The problem is that this is SLOW, for each of the 1000 items in the pivotfield, it recalculates the pivottable (even if calculation is set to manual), even if its Visible status did not change. There should be a much faster solution since really, I only want to hide 55 (or whatever other new fields may have slipped in). So I wrote:
Code:
Sub CheckMultItemsFAST()
Dim PVTITM as PivotItem
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
If PVTITM.Name = "9999" Or PVTITM.Name = "99999" Then
If PVTITM.Visible = False Then
PVTITM.Visible = True
End If
Else
If PVTITM.Visible = True Then
PVTITM.Visible = False
End If
End If
Next PVTITM
End Sub
The problem here is that PVTITM.Visible is ALWAYS FALSE. This apparently is true for any Page Field. I could write
Code:
For Each PVTITM In Sheets("Pivots").PivotTables("PivotTable2").PivotFields("Catlogs").PivotItems
PVTITM.Visible = True
Debug.Print PVTITM.Visible
Next PVTITM
Thanks,
~Gold Fish