Cycle Through Pivot Items

[2696883]

This could be quite a simple question, but I am trying to cycle through the items on a PivotField
using VBA. I have written:

For Each Item In PT.PivotFields("Country")
'Code inserted here
Next Item

However Item (and Page) seems to be an undefined variable. I would like to cycle through each country (Albania, Belgium, Canada etc) and run a macro for the data associated with that country. What variable should I be using?

 

[NARAYANK991]

Hi ,

Can you try this ?

    Set PT = .PivotTables("PivotTable1")            ' Change this to suit
     For Each fld In PT.PivotFields
        If fld.Name = "Country" Then
'          Code inserted here
        End If
    Next fld

Narayan

 

[2696883]

Thanks Narayan991, but that doesn't work and isn't exactly what I am trying to do. Fld is not a recognised variable, and I would need to create many If functions for each country - given that the list of countries is both long and dynamic (subject to change from time to time and from worksheet to worksheet), I would rather not list each one individually and instead cycle through each item present in the PivotField.

Hi ,

Can you try this ?

    Set PT = .PivotTables("PivotTable1")            ' Change this to suit
     For Each fld In PT.PivotFields
        If fld.Name = "Country" Then
'          Code inserted here
        End If
    Next fld

Narayan

 

[NARAYANK991]

Hi ,

I do not know what you are trying to do ; can you upload a sample workbook ?

fld is just a placeholder ; just as we normally write :

For each cell in Range("A2:A34")

where cell is just a placeholder , and does not have to be declared unless you have used Option Explicit in your project ; if so , then cell would be declared as a variable of type Range.

In your example , fld would be declared as of type PivotField as follows :

Dim fld as PivotField

Narayan

 

[2696883]

A sample workbook is attached. I would like the macro to loop through each Country in the PivotField and run some code for each of those countries (in this example, the code is limited to a Message Box).

Hi ,

I do not know what you are trying to do ; can you upload a sample workbook ?

fld is just a placeholder ; just as we normally write :

For each cell in Range("A2:A34")

where cell is just a placeholder , and does not have to be declared unless you have used Option Explicit in your project ; if so , then cell would be declared as a variable of type Range.

In your example , fld would be declared as of type PivotField as follows :

Dim fld as PivotField

Narayan

 

[NARAYANK991]

Hi ,

See your file.

Narayan