Create a List of Pivot Table Report Filter Selections on Worksheet

[JimS]

Does anyone have a way to "read or obtain" the items selected by a user on a Pivot Table Report Filter and then create a list of all the users selection items on a seperate worksheet?

Everytime the user changes the PT Filter selection the list would automatically be updated on the worksheet.

Multiple items maybe selected.

Thanks for any and all help...

JimS

[Paul_Hossler]

You can use the PT Update event to trigger the generation

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   Call TEST(Target)
End Sub

and

Option Explicit
Sub TEST(pt As PivotTable)
    Dim ptItem As PivotItem
    Dim i As Long
    
    i = 1
    
    With pt
        For Each ptItem In .PivotFields("AAA").PivotItems
            If ptItem.Visible Then
                .Parent.Cells(i, 14).Value = ptItem.Value
                i = i + 1
            End If
        Next
    End With
End Sub

This is kind of hard coded, but you could generalize it pretty easily. I just made a simple PT and listed the selected Page Fields in column O

Wasn't sure exactly what you were looking for, but this seems to have the basics

Paul

[JimS]

Thanks Paul - just what I needed...

[fredlo2010]

Hi,

Just a little to add to Paul awesome reply. Maybe you want to clear what was listed first before populating a new list.

Option Explicit

Sub TEST(pt As PivotTable)
    Dim ptItem As PivotItem
    Dim i As Long
    
    ' clear previous items
    Range("N1:N" & Cells(Rows.Count, "N").End(xlUp).Row).ClearContents
    
    ' do the pivot thing
    i = 1
    
    With pt
        For Each ptItem In .PivotFields("AAA").PivotItems
            If ptItem.Visible Then
                .Parent.Cells(i, 14).Value = ptItem.Value
                i = i + 1
            End If
        Next
    End With
End Sub

Thanks