Excel Pivot table - values for filtered fields

Question

I'm looking for some VBA to simply display those fields of my pivot table and the values of the fields that may have been filtered by the user.

In this case there may be multiple filters applied and multiple values in the filters.

It seems like this should be a relatively easy find but I'm not finding anything helpful when searching.

Can anyone point me in the right direction?

This gets me the Pivot tables on the sheet with the list of fields for each.. how do I get the filters applied to each field??

Sub test()
Set Sht2 = Sheets("8800")
With Sht2
    For Each tbl In .PivotTables
       For Each fld In tbl.PivotFields
             Debug.Print tbl.Name & " - " & fld.Name
       Next fld
    Next tbl
End With
End Sub

VB,

Adam Marx Adam Marx · Accepted Answer · 2020-08-31T11:00:25

This seems to work for me in case anyone is looking for a similar answer.

   With sh
        For Each tbl In .PivotTables
           For Each fld In tbl.PivotFields
                If tbl.PivotFields(fld.Name).AllItemsVisible = False Then
                    For Each fltr In tbl.PivotFields(fld.Name).VisibleItems
                        Debug.Print tbl.Name & " - " & fld.Name & " - " & fltr.Name
                        'Debug.Print x.Name
                    Next fltr
                End If
           Next fld
        Next tbl
    End With

Excel Pivot table - values for filtered fields

1 Answers