Matching Pivot Table DISPLAY Order to PivotItem Indexing Order

[DeadGoat]

Hello All,
Objective: Seeking solution to extract Pivot table index Item value that matches display order.

Description:Simple For Each loop that indexes through the Pivot Table return pivotItems based on ascending order.

What I need is a way for vba to return pvtItem content based on the pivotTable DISPLAY order.

Any help is greatly appreciated.
PivotTable DISPLAY order example:

[TABLE="width: 167"]
<tbody>[TR]
[TD]Category1[/TD]
[TD]Category2[/TD]
[/TR]
[TR]
[TD]Boat[/TD]
[TD]T24TTJ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]UFEGE[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]J45631

[/TD]
[/TR]
</tbody>[/TABLE]


...
For Each pvtField In pvtTable.PivotFields

For Each pvtItem In pvtField.PivotItems
debug.print pvtItem
Next pvtItem
​

Next pvtField
...

Result:
J45631
T24TTJ
UFEGE

If I look query in the vba debugger I see:
Pvtfield.pivotItems(1).Name
J45631
Pvtfield.pivotItems(2).Name
T24TTJ
Pvtfield.pivotItems(3).Name
UFEGE

This is all wonderful, but I don't want PivotTable to sort when I index through the items. I want VBA index to return based on Display as:

Pvtfield.pivotItems(1).Name
T24TTJ
Pvtfield.pivotItems(2).Name
UFEGE
Pvtfield.pivotItems(3).Name
J45631


Any suggestions on how this can be done.

Thank you!

 

[Andrew Poulsom]

Maybe try:

Sub Test()
    Dim r As Long
    With ActiveSheet.PivotTables(1).RowRange
        For r = 2 To .Rows.Count
            Debug.Print Cells(r, 2).Value
        Next r
    End With
End Sub