Add Slicer Selections to sheet in a list - Excel VBA

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

1 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")

Public Function SlicerItems(SlicerName As String, Optional sDelimiter As String = "|") As String Dim oSc As SlicerCache Dim oSi As SlicerItem Dim i As Long Dim lVisible As Long Dim sVisible() As String On Error Resume Next Application.Volatile Set oSc = ThisWorkbook.SlicerCaches(SlicerName) If Not oSc Is Nothing Then With oSc If .FilterCleared Then SlicerItems = "(All)" Else If .OLAP Then SlicerItems = Join(.VisibleSlicerItemsList, sDelimiter) SlicerItems = Replace(SlicerItems, .SourceName, "") SlicerItems = Replace(SlicerItems, ".&[", "") SlicerItems = Replace(SlicerItems, "]", "") Else lVisible = .VisibleSlicerItems.Count If .VisibleSlicerItems.Count = 1 Then SlicerItems = .VisibleSlicerItems(1).Name Else ReDim sVisible(1 To lVisible) For i = 1 To lVisible sVisible(i) = .VisibleSlicerItems(i).Name Next i SlicerItems = Join(sVisible, sDelimiter) End If End If End If End With Else SlicerItems = SlicerName & " not found!" End If
End Function

And here's how it looks: