VBA macro: Loop through pivot columns

[gio123bg]

Hi All,
I have the necessity to check the values in two columns of two pivot tables. The pivot tables have the same layout. Is it possible to have an example of VBA code? I tried to find it but without success.

Any help will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[Jerry Sullivan]

Hi Giovanni,

Here is an example showing how to step through all PivotTables in a specified Worksheet and process each value in two named datafields.

Note there isn't any error handling, so you will get an unhandled error if both datafields aren't found in each Pivot Table.

Sub PT_Values()
    Dim PT As PivotTable
    Dim i As Long
    Dim c As Range
    Dim varFields As Variant
    
    varFields = Split("Sum of Sales;Count of Stores", ";")
        
    For Each PT In Sheets("Sheet1").PivotTables
        For i = LBound(varFields) To UBound(varFields)
            With PT.DataFields(varFields(i))
                For Each c In PT.DataBodyRange.Resize(, 1) _
                        .Offset(0, .Position - 1)
                    'for testing demo only....
                    MsgBox PT.Name & "> " & varFields(i) & "> " & c.Value
                Next
            End With
        Next i
    Next PT
End Sub