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.
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?
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.
![zAh4Q.png](/board/proxy.php?image=https%3A%2F%2Fi.stack.imgur.com%2FzAh4Q.png&hash=2195253e4e7c10b5870333479f1db6ac)
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.
Code:
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?