For next loop doesn't work with certain PivotFields

[szakharov7723]

When I use this code

Sub FormatTest1()
With ActiveSheet.PivotTables("PriceHistory").PivotFields("Description")
    For i = 1 To .PivotItems.Count - 1
    .PivotItems(.PivotItems(i).Name).DataRange.Select
    Next i
End With
End Sub

It selects single total field. And loops through each (item labels hidden)

But when I try to use this code , it doesn't work

However this one selects all fields resulting in error later.

Sub FormatTest1()
With ActiveSheet.PivotTables("PriceHistory").PivotFields("Years")
    For i = 1 To .PivotItems.Count - 1
    .PivotItems(.PivotItems(i).Name).DataRange.Select
    Next i
End With
End Sub

How can I select all dates for each item field ?

 

[Roger Govier]

I suspect it is because your Date field has been Grouped, whereas your other fielda re ungrouped.

 

[szakharov7723]

I suspect it is because your Date field has been Grouped, whereas your other fielda re ungrouped.

I suspect so, but I don't know how to make it work for months. There don't seem to be phantom "Months".

 

[Roger Govier]

I think the only way around would be to add a column in your source data Called Months and have a formula there to calculate the month for each transaction. Then use Month in your PT instead of Date.

 

[szakharov7723]

I think the only way around would be to add a column in your source data Called Months and have a formula there to calculate the month for each transaction. Then use Month in your PT instead of Date.

I think it may work to some extent, I will try to play with this to change loop method for item field