I have a pivot table With a slicer that a user can make multiple selections in. I am trying to list the selected values in the slicer so they can then be joined together in another cell using CONCATENATE. I am using the code below.
At the moment, cells L5:L7 are populated with the first selection made in the slicer, but no others.
I did some research and found a possible solution with the CUBESET function, but I can't get it to work in my spreadsheet. Hence the VBA attempt. ANyone know whats wrong with it?
Sub City_Click()
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_City")
Dim sItem As Excel.SlicerItem
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then Range("L5").Value = sItem.Name
If sItem.Selected = True Then Range("L6").Value = sItem.Name
If sItem.Selected = True Then Range("L7").Value = sItem.Name
Next sItem
End Sub
Best Answer
Here's a User Defined Function that you can call directly from the workbook that does this for you, and can be run on any type of Slicer, be it a 'traditional' PivotTable, an OLAP/PowerPivot PivotTable, or a Table Slicer. Just put this in a standard code module, and then in the workbook type the following:
=SlicerItems("Slicer_City")
And here's how it looks: