Finding a subset of a Pivot table rowfield

Question

I'm trying to use VBA to extract and manipulate data from a pivot table. My pivot table has three Row labels (D_Campaign, D_TL, D_Agent_Name).

Using

For Each pt1 In pt.RowFields("D_Agent_Name").PivotItems
    Debug.Print pt1.Name
Next pt1

I can get a list of all the labels in 'D_Agent_Name'. However, what I would like, is a list of the labels in 'D_Agent_Name' for a given value of 'D_TL'. Can anyone point me in the right direction?

Answer 1

I think @gimp may be right that it's easier to deal with the source data, but here's something that works for me:

Sub PrintTLValues(AgentName As String)
Dim pt As Excel.PivotTable
Dim rfTL As Excel.PivotField
Dim rfAN As Excel.PivotField
Dim cell As Excel.Range

Set pt = ThisWorkbook.Worksheets(1).PivotTables(1)
Set rfAN = pt.RowFields("D_Agent_Name")
Set rfTL = pt.RowFields("D_TL")
For Each cell In Intersect(pt.DataBodyRange.EntireRow, rfAN.DataRange)
    If cell.Value = AgentName Then
        Debug.Print Intersect(cell.EntireRow, rfTL.DataRange).Value
    End If
Next cell
End Sub

Call it like this:

GetTLValues "Michael"

I like programming pivot tables, and my experience is that there's usually more than one way to skin a pivot table cat :). A very helpful reference for programming to pivot table ranges can be found on Jon Peltier's site.

Answer 2

Got there in the end after Doug pointed me in the right direction.

Sub ptSelect()
    ' Select intersect of pivot table and output cell values to debug window
    Dim pt As PivotTable
    Dim ptSel As Range

    Set pt = Worksheets("Pivot Table").PivotTables("PT Sales Data")
    Set ptSel = Intersect(pt.PivotFields("D_Campaign").PivotItems("Acquisition").DataRange.EntireRow, pt.PivotFields("D_TL").DataRange)

    For Each Cell In ptSel
        Debug.Print Cell.Value
    Next Cell
End Sub

One thing I have noticed is that this requires the pivot table to be in the classic format, it won't work with the compact format in 2007.