How to cycle through displayed PivotItems instead of theVisibleItems collection
I stumbled over a similar problem recently while trying to save
specific pivot table layouts. I solved it by using a case select and
making the pagefield temporarily a rowfield.
....
Dim pt as PivotTable
Dim objPF As PivotField
Dim pfItem As PivotItem
....
For Each objPF In pt.VisibleFields
Select Case objPF.Orientation
Case xlRowField
For Each pfItem In objPF.PivotItems
If pfItem.Visible = True Then
...
End If
Next pfItem
Case xlColumnField
For Each pfItem In objPF.PivotItems
If pfItem.Visible = True Then
...
End If
Next pfItem
Case xlPageField
objPF.Orientation = xlRowField
For Each pfItem In objPF.PivotItems
' Debug.Print pfItem.Parent.Name, pfItem.Name,
pfItem.Visible
If pfItem.Visible = True Then
....
End If
Next pfItem
objPF.Orientation = xlPageField
Case xlDataField
Case xlHidden
End Select
Next objPF
|