Get a given pivot item of the second row field given a particular pivot item in the first row field

Question

I have a pivot table with two embedded row labels and one data variable (see below). I would like to access the pivot item of Rowlabel2 of a given index, given a particular pivot item of Rowlabel1.

This question (List excel pivot items of the second row field (multiple row fields) given a particular pivot item in the first row field) is very close to my problem but not exactly what I need. I hope there exists a way to get the item without resorting to a separate sub routine.

Rowlabel1 Rowlabel2 Value
a          A          1
           B          0
           C          3
b          D          2
           E          8
c          F          5

For instance, I would like to get the 2nd item of Rowlabel2 of the 2nd item of Rowlabel1 ("E"). The property RowFields("Rowlabel2").PivotItems(2).Caption

returns "B", not "E"

Answer 1

You may loop over the rows of your pivottable, which are PivotTable.PivotRowAxis.PivotLines.
Their first left PivotLineCells contain RowField.PivotItems, which can be compared and counted.

Private Sub GetIt()
    MsgBox GetPivotItem(ActiveSheet.PivotTables(1), 2, 2)
End Sub

Private Function GetPivotItem(ByRef pt As PivotTable, _
  ByRef index1 As Long, ByRef index2 As Long) As String
    Dim pl As PivotLine
    Dim counter1 As Long, counter2 As Long
    Dim remember1 As String, remember2 As String

    For Each pl In pt.PivotRowAxis.PivotLines
        If pl.LineType = xlPivotLineRegular Then
            If pl.PivotLineCells(1).PivotItem.Caption <> remember1 Then
                remember1 = pl.PivotLineCells(1).PivotItem.Caption
                remember2 = pl.PivotLineCells(2).PivotItem.Caption
                counter1 = counter1 + 1
                counter2 = 1
            ElseIf pl.PivotLineCells(2).PivotItem.Caption <> remember2 Then
                remember2 = pl.PivotLineCells(2).PivotItem.Caption
                counter2 = counter2 + 1
            End If
            If counter1 = index1 And counter2 = index2 Then
                GetPivotItem = pl.PivotLineCells(2).PivotItem.Caption
                Exit For
            End If
        End If
    Next pl
End Function

---

Above works for the pivottable layout with rowfields in separate columns (like your screenshot, PivotLine.PivotLineCells.Count > 1).
If you switch to the layout with rowfields indented in the same column, use this instead:

Private Function GetPivotItemNew(ByRef pt As PivotTable, _
  ByRef index1 As Long, ByRef index2 As Long) As String
    Dim pl As PivotLine
    Dim counter1 As Long, counter2 As Long

    For Each pl In pt.PivotRowAxis.PivotLines
        If pl.LineType = xlPivotLineRegular Then
            If pl.PivotLineCells(1).PivotField = pt.RowFields(1) Then
                counter1 = counter1 + 1
                counter2 = 0
            End If
            If pl.PivotLineCells(1).PivotField = pt.RowFields(2) Then counter2 = counter2 + 1
            If counter1 = index1 And counter2 = index2 Then
                GetPivotItemNew = pl.PivotLineCells(1).PivotItem.Caption
                Exit For
            End If
        End If
    Next pl
End Function

Answer 2

Public Function getPiv(pt As PivotTable, iRowlabel1 As Integer, iRowlabel2 As Integer) As Variant

'returns item # iRowlabel2 of second Row field of item # iRowlabel1 of first Row field


Dim r As Range
Dim dr As PivotItem

'this captures your first entry in rowlabel1 - change as necessary
Set dr = pt.PivotFields("Rowlabel1").PivotItems(iRowlabel1)

'the macro won't work if the item is collapsed, so we set showDetail to true to expand it
If dr.ShowDetail = False Then
    dr.ShowDetail = True
End If

With dr
    'address what seems to be a bug(??) where if there are more than one data value columns,
    'the datarange appears to shift one row down
    If .DataRange.Columns.Count > 1 Then
        'here we shift back from the datarange to the 2nd column of the pivottable
        Set r = .DataRange.Offset(-1, -(.DataRange.Cells(1, 1).Column - 2))
    Else
        'here we shift back from the datarange to the 2nd column of the pivottable
        Set r = .DataRange.Offset(0, -(.DataRange.Cells(1, 1).Column - 2))
    End If
End With


getPiv = r.Cells(iRowlabel2, 1)


End Function