Building on this question, I have a workbook with a pivot table from an OLAP cube. I need to be able to change a slicer for this pivot using the values in a selected range of cells. I can make the solution provided in the linked question work for one value, but I get a type mismatch error when trying to use an array.
This is the code I'm using. The [Branch Name] is the field I'm trying to set.
Dim sel_Array As Variant
Dim rng As Range
'Set rng = Selection
sel_Array = Selection.Value
ActiveWorkbook.SlicerCaches("Slicer_Branch_Name").VisibleSlicerItemsList = Array("[Dim Location].[Branch Name].&[" & sel_Array & "]")
I also tried using the rng variable to put "rng.Value" into the VisibleSlicerItemsList, but that didn't work either.
You need to create an array like the following, which will handle both single-cell and multi-cell inputs:
Private Function VisibleItemsList(ByVal rng As Range) As Variant
Const mdx As String = "[Dim Location].[Branch Name].&["
If rng.CountLarge = 1 Then
VisibleItemsList = Array(mdx & rng.Value & "]")
Exit Function
End If
Dim arr() As Variant
ReDim arr(0 to rng.Cells.CountLarge - 1)
Dim data() As Variant
data = rng.Value
Dim i As Long, j As Long
For i = LBound(data, 1) to UBound(data, 1)
For j = LBound(data, 2) to Ubound(data, 2)
Dim counter As Long
arr(counter) = mdx & data(i, j) & "]"
counter = counter + 1
Next
Next
VisibleItemsList = arr
End Function
Call it like this:
ActiveWorkbook.SlicerCaches("Slicer_Branch_Name") _
.VisibleSlicerItemsList = VisibleItemsList(Selection)