Excel iterate through nested pivot table rowfields with VBA

4
votes

I have a Pivot Table and I need to iterate through nested RowFields:

Example Pivot Table

How can I parse the nested RowFields?

My code so far is:

Sub PtRead()
    Dim i As Integer, j As Integer
    Dim Sh As Worksheet
    Dim pt As PivotTable
    Dim rngRow As Range

    Set Sh = Sheets("Store")
    Set pt = Sh.PivotTables(1)

    i = 3
    For j = 1 To pt.RowFields(i).PivotItems.Count
        Debug.Print pt.RowFields(i).PivotItems(j)
    Next

End Sub

I need to retrieve, for example, all the nested article codes related to brand "AAA" and the related Nsum value with something like (this doesn't work..):

...    
pt.RowFields(1).PivotItems(1).RowFields(2).PivotItems(j)
...
1
excelpivot-tablevba

1 Answers

0
votes

I think you want to loop through the Pivot Items. Look at the code below for some guidance on how to proceed.

Sub TryMe()

For Each Pi In ActiveSheet.PivotTables("PivotTable1").PivotFields("DeptHead").PivotItems
    If Pi.Visible Then
        Count = Count + 1
        Range("B" & Count).Value = Pi.Name
        Row = Row + 1
    End If
Next Pi

End Sub

The Macro Recorder is very helpful if you get stuck on something.