Displaying PivotTable Filter Selections in New Range

[PRobinson87]

I am using Excel 2010 and have a need to visually see all the selections made in the pivot filters.

I am using Analysis Services to create a PivotTable. Specifically, I am displaying information by Product Number and would like to have an output next to the pivot that shows the Products selected. Is this possible? I've looked around on this site as well as others and haven't seen a question like this one before. I have inserted a Slicer and am currently using it reversed from its intended purpose. I am filtering within the Drop Down box on the Pivot Table and showing which have been selected on the Slicer. The problem with this is I have about 15,000 different Products and it is not convenient when I am selecting products that are not near each other in the sort order and I have to scroll up and down.

Any help would be appreciated.

Thanks!

 

[ChrisM]

Give this a shot:

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

Dim i As Long
Dim pFld As PivotItem

i = 1

With Target
    For Each pFld In Target.PivotFields("Group").PivotItems
       If pFld.Visible = True Then
           Sheet6.Cells(i, 10) = pFld.Value
           i = i + 1
       End If
    Next pFld
End With

Set pFld = Nothing

End Sub

change "Group" to the pivot field name you want to output, probably "Product"

Then change Sheet6.Cells(i,10). Change Sheet6 to your sheet name, and change i,10 to the location you want to start outputting the list to. I have it starting in J1 right now. if you want to start in a row other than 1, change i = 1 to whatever number you want to start with.

 

[PRobinson87]

Thank you for the quick response. I am getting a syntax error on the line 10 : For Each pFld In Target.PivotFields(LII SKU).PivotItems Am I missing something?

 

[ChrisM]

Yes, you left out the quotes:

For Each pFld In Target.PivotFields("LII SKU").PivotItems

 

[PRobinson87]

This is what I get and when I debug it highlights line 10:

Run-Time Error '1004':
Unable to get the PivotFields property fo the PivotTable Class

 

[ChrisM]

You added the quotes in?

Where is LL SKU physically located in your pivot table? column, row, or filter section?

 

[PRobinson87]

Chris,

Yes, I did add the quotes. The LII SKU field is located in the report filter section. This data is pulled and the pivot created from Analysis Services. Would have play a role in the issues I'm experiencing?

 

[ChrisM]

Sorry, not familiar with Analysis Services. I tested my code on a normal pivot table and it works fine, not sure why it doesn't work for you. My only guess based on the error message is that you are typing the field name wrong, but that doesn't seem very likely. Just for fun, try replacing that line with this though:

For Each pFld In Target.PivotFields(1).PivotItems

 

[PRobinson87]

With the replacement line it ran but there wasn't an output.

 

[ChrisM]

That indicates that it is a spelling issue. You can refer to fields with their name or with their index number, you get the same result either way. I had you try "1", but that's not necessarily the index number of the "LII SKU" field. Forget the number for now, that was just a test, we have to figure out why you can't write the field name out. Are you sure there are not some leading/trailing spaces, perhaps like " LII SKU "? Right click the LII SKU field name, choose field setttings, and verify what you see listed as the field name in the window. The text will be highlighted in blue and you should be able to see any leading/trailing spaces.