VBA Code to return pivot table cell's row, column, and page fields and items

Question

I'm trying to write some code that will return the row, column, and page fields and items after I've highlighted a cell in the values area of the pivot table. For example, if I select a cell that contains a value of $1000, I'd like to the code to pull the row field and item (for example, the field would be Department and item would be Finance), column field and item (for example, the field would be Account and item would be Advertising) and page field and item (for example, the field would be Company and item would be XYZ, Inc.).

This seems like it should be pretty straightforward, because when you hover over any cell in a pivot table, it will show you the drilldown information in the contextures box, however, I'm having a hard time manipulating the pivot table objects because there doesn't seem to be much written on them online or by microsoft.

It seems like the pivotline or pivotlines object might be what I'm looking for, but I can't figure out how to use it.

I originally took this approach and it worked fine until I realized that a rowfields' index is not necessarily it's position in the row field, so I had to scrap this.

Sub ActualDetailDrill()
'after sub is completed, make this sub called when a cell in a pivot table is double-clicked.

Dim NumOfRowItems As Integer
Dim NumOfColItems As Integer
Dim NumOfPageFields As Integer
Dim Field As PivotField
Dim ActualDrillActiveCell As Range

Set ActualDrillActiveCell = Sheets("ActualDrill SQL Build").Range("A1")
NumOfRowItems = ActiveCell.PivotCell.RowItems.Count
i = 1

Do Until i > NumOfRowItems
    ActualDrillActiveCell.Value = ActiveCell.PivotTable.RowFields(i).Name
    ActualDrillActiveCell.Offset(0, 1).Value =    ActiveCell.PivotCell.RowItems(i).Name
    ActualDrillActiveCell = ActualDrillActiveCell.Offset(1, 0)
    i = i + 1
Loop

End Sub

Any help would be very, very appreciated. This is one of the last steps in a big project I'm working on that will be very helpful to the company I work for.

Here's must-read post for this type of code: peltiertech.com/referencing-pivot-table-ranges-in-vba — Doug Glancy
Thanks Doug. This is very helpful but I'm trying to do the reverse of what is talked about in this link. The article assumes you know the field and items names you want to pull data back for. I already know the data range (one cell rather than multiple cells) and want the field (column, row, and page) and items for that data range. Would the author, Jon, be a good person to ask about this since he seems familiar with the code for pivot tables? Thanks! — jones-chris

Doug Glancy Doug Glancy · Accepted Answer · 2016-01-16T18:52:16

It's the weekend and I had some time to dig into this interesting question. I think you were pretty close by using RowItems and ColumnItems. The PivotTable.RowFields are more general though and don't apply at the PivotCell level.

I hate working with Page Fields, but think this is the correct logic for that:

Sub GetValueFieldStuff()
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentItem As Excel.PivotField
Dim i As Long

On Error Resume Next
Set pvtCell = ActiveCell.PivotCell
If Err.Number <> 0 Then
    MsgBox "The cursor needs to be in a pivot table"
    Exit Sub
End If
On Error GoTo 0

If pvtCell.PivotCellType <> xlPivotCellValue Then
    MsgBox "The cursor needs to be in a Value field cell"
    Exit Sub
End If

Set pvtTable = pvtCell.PivotTable
For Each pvtField In pvtTable.PageFields
    i = 0
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Visible Then
            i = i + 1
            Debug.Print "PageField " & pvtField.Name & " - Pivot Item " & i & " is " & pvtItem.Name
        End If
    Next pvtItem
Next pvtField

Debug.Print "Value Field Name is " & pvtCell.PivotField.Name
Debug.Print "Value Field Source is " & pvtCell.PivotField.SourceName

For i = 1 To pvtCell.RowItems.Count
    Set pvtParentItem = pvtCell.RowItems(i).Parent
    Debug.Print "Row Item " & i & " is " & pvtCell.RowItems(i).Name & ". It's parent Row Field is: " & pvtParentItem.Name
Next i

For i = 1 To pvtCell.ColumnItems.Count
    Set pvtParentItem = pvtCell.ColumnItems(i).Parent
    Debug.Print "Column Item " & i & " is " & pvtCell.ColumnItems(i).Name; ". It's parent Column Field is: " & pvtParentItem.Name
Next i
End Sub

If you aren't already, consider using the VBE's Locals Window. It's great for drilling down (and back up) in the pivot table's object model. That's how I saw that a ColumnItemis a PivotItem whoseParent is the PivotField it's in.

VBA Code to return pivot table cell's row, column, and page fields and items

1 Answers