VBA Loop PivotTable

[2696883]

I use the below code to loop through each country in a PivotTable.

Sub AutoLoop()
Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables(1)
Dim Country As PivotItem
ForEach Country In PT.PivotFields("Country Name").PivotItems
MsgBox PT.PageFields("Country Name").DataRange.Value
Next Country
EndSub

However the message box always returns the value 'All' for each of the countries, as that is the default selection in the 'Country Name' PageField. It does not loop through and give the country name for each of the countries. If I manually select a country, then the message box will always return that country. How do I get the MsgBox code to factor in the loop?
In essence, what I need is for the DataRange of the PageField to change and show the current country, rather than just have the countries loop through with no change to the DataRange in the PivotTable.

 

[NARAYANK991]

Hi ,

I do not know what you want to do , but since you are selecting each country using the following statement :

For Each Country In PT.PivotFields("Country Name").PivotItems

can you not display the country's name , by the following ?

Msgbox Country

Narayan