Add Slicer Selections to sheet in a list - Excel VBA
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:
Related videos on Youtube
user767772
Updated on September 18, 2022Comments
-
user767772 almost 2 years
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
-
jeffreyweir over 6 yearsCool. I forgot to say that there's an optional second argument that specifies the delimiter to use. The default is the pipe character | but you can specify others such as a comma.