Cycle through Pivot Table values -

[Dennis_S]

Hello,
I'm trying to cycle trough values in very simple Pivot Table. Two rows and one Sum value.
Now I see the very strange shift during the cycle - please see the screenshot.

Yellow outlined cells are shifted - with the incorrect "Month". Red outlined cells are the duplicates - they are the copy of green range. And the green ones are correct till the end of output. The code is pasted below. Any combinations of pi / pi2 and Caption / Value in the cycle are giving the similar incorrect result but I can not obtain the correct one.

Sub jayd12()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pf2 As PivotField
    Dim pi As PivotItem
    Dim pi2 As PivotItem
    
    Set pt = Worksheets("CaissePivot").PivotTables("PivotCaisse")
    Set pf = pt.PivotFields("Year")
    Set pf2 = pt.PivotFields("Month")
    For Each pi In pf.PivotItems
        For Each pi2 In pf2.PivotItems
            If pi.DataRange(pi2.Position, 1).Value <> "" Then _
            Debug.Print pi.Value & "  " & pi2.Caption & "  " & pi.DataRange(pi2.Position, 1).Value
        Next pi2
    Next pi
End Sub

I understand that I'm wrong in using members and collection items.
Could anyone explain how to cycle through the headers and values in the Pivot?

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe...

Sub jayd12()
    Dim pt As PivotTable
    Dim rCell As Range
    
    Set pt = Worksheets("CaissePivot").PivotTables("PivotCaisse")
    
    For Each rCell In pt.PivotFields("Year").DataRange.Cells
        Debug.Print rCell & " " & rCell.Offset(, 1) & " " & rCell.Offset(, 2)
    Next rCell
End Sub

Hope this helps

M.

 

[Dennis_S]

Marcelo,
It's perfect! Thanks a lot!