PDA

View Full Version : [SOLVED] Create a List of Pivot Table Report Filter Selections on Worksheet



JimS
12-10-2013, 12:18 PM
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
12-10-2013, 06:11 PM
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
12-11-2013, 09:42 AM
Thanks Paul - just what I needed...

fredlo2010
12-20-2013, 01:12 PM
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