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