extract data value from Pivot filter (Multiple Items)

[yessuz]

Hi Guys,

i wonder if there is any way to extract the value of the filter on the pivot table when you select more than 1 criteria?

For example:
If my filter criteria has "apples", "Pears", "oranges" and I select "Apples" and "pears" in the filter criteria, the value of the filter is shown as (Multiple Items)
If I select "Oranges" - the value of filter is shown as Oranges.

then I can use the formula to get the cell value of the cell.

But when the multiple items is selected, the value is just (Multiple Items).

Is there any way to extract the value of filter criteria in order to get Apples and Oranges as the result?

thanks!!!

 

[John_w]

Try this UDF:

Public Function Get_PT_Field_Items(PivotTableName As String, PivotField As String) As String

    Dim PT As PivotTable
    Dim PTfield As PivotField
    Dim PTitem As PivotItem
    
    Set PT = ActiveSheet.PivotTables(PivotTableName)
    Set PTfield = PT.PivotFields(PivotField)
    
    Get_PT_Field_Items = ""
    For Each PTitem In PTfield.PivotItems
        Debug.Print PTitem.Name
        If PTitem.Visible Then Get_PT_Field_Items = Get_PT_Field_Items & PTitem.Name & ", "
    Next
    
    If Get_PT_Field_Items <> "" Then
        Get_PT_Field_Items = Left(Get_PT_Field_Items, Len(Get_PT_Field_Items) - 2)
    End If
    
End Function

Use it like this in a cell formula:

=Get_PT_Field_Items("PivotTable1","Fruit")

where "PivotTable1" is the name of the pivot table and "Fruit" is the name of the field.

However, the above cell formula is not recalculated when you change the filter field items. Instead you have to call it from the Worksheet_PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Range("H1").Value = Get_PT_Field_Items(Target.Name, "Fruit")
End Sub

where H1 is the cell you want to contain the list of selected items.