Excel VBA to get item detail of selected Pivot Table row

Question

OK, I've been digging through a lot of resources, but I can't find any clues on how to achieve this. I've dug through the object model but none of the properties or methods appear to give me what I need.

I want to get the row data of the selected row. I don't want to extract the data onto a new sheet, I just want to get the Row Label of the active row. I then put that row data into a sheet that displays the details in a better format than just the extracted method.

To simplify the example, my pivot has:

Row Labels: Case_Name, Case_Ref (1:1 relationship)
Column Labels: Status
Values: Count of Calls

Ideally, I'd like to have Case_Ref hidden from view in the Pivot, but it is the key I need to extract for the next step.

I want the user to select one item (row) in the pivot, then press my button and have the macro return the Case_Ref of the selected row.

My (unsuccessful) code so far looks like this:

Sub Pivot_Detail_By_Case__Customer_Contacts_By_Case()
    'CCBC = Customer Contacts By Case
    Set CCBC_PivotTable = Worksheets("Customer contacts by case").PivotTables(1)
    HeaderRow = CCBC_PivotTable.RowRange.Row
    RowCount = CCBC_PivotTable.RowRange.Count

    'Confirm limits of row range
    'Range(Cells(HeaderRow + 1, 1), Cells(HeaderRow + RowCount - 2, 1)).Select

    PivotDataStartRow = HeaderRow + 1
    PivotDataEndRow = HeaderRow + RowCount - 2

    If ActiveCell.Row >= PivotDataStartRow And ActiveCell.Row <= PivotDataEndRow Then
        ItemNumber = ActiveCell.Row - PivotDataStartRow
        'SelectedField = CCBC_PivotTable.DataPivotField.PivotItems(ItemNumer)
        'pvt_InnerDetail = CCBC_PivotTable.InnerDetail
        'SelectedField = CCBC_PivotTable.RowFields("Case_Ref")
    End If

End Sub

The ItemNumber appears to work, but how do I get the Case_Ref from there?

Thanks for your help.

I won't add as an answer as it's a link to an external site. This outlines how to reference different parts of a pivot table: peltiertech.com/referencing-pivot-table-ranges-in-vba — Darren Bartrup-Cook

Scott Scott · Accepted Answer · 2017-02-22T14:11:20

Ok, managed to crack it. Found another example showing how to loop through the items and extrapolated that. It was initially a bit confusing because the item number is not the order in the table so didn't correlate to the selected row.

Revision to previous answer: If an item wasn't visible (hidden by active filter), then PivotItem.DataRange.Row threw an error. Now use error testing to see if row is visible:

 Sub Pivot_Detail_By_Case__Customer_Contacts_By_Case()
    'CCBC = Customer Contacts By Case
    Set CCBC_PivotTable = Worksheets("Customer contacts by case").PivotTables(1)
    HeaderRow = CCBC_PivotTable.RowRange.Row
    RowCount = CCBC_PivotTable.RowRange.Count

   'Confirm limits of row range
   'Range(Cells(HeaderRow + 1, 1), Cells(HeaderRow + RowCount - 2, 1)).Select

    PivotDataStartRow = HeaderRow + 1
    PivotDataEndRow = HeaderRow + RowCount - 2


    If ActiveCell.Row >= PivotDataStartRow And ActiveCell.Row <= PivotDataEndRow Then
        For Each PivotItem In CCBC_PivotTable.PivotFields("Case_Ref").PivotItems
            'Debug.Print PivotItem.DataRange.Row
            'PivotItem.DataRange.Row throws an error if the item is hidden by the active filter
            Err.Number = 0
            'Debug.Print Err.Number
            TestIfVisible = PivotItem.DataRange.Row
            'Debug.Print Err.Number
            If Err.Number = 0 Then 'Last line didn't cause an error, i.e. item is visible
                If PivotItem.DataRange.Row = ActiveCell.Row Then
                    SelectedCallRef = PivotItem.Value
                    GoTo RowFound
                End If
            End If
        Next
RowFound:
    End If

    MsgBox SelectedCaseRef

End Sub

Excel VBA to get item detail of selected Pivot Table row

1 Answers