title: How to select Slicer Items with an array in VBA
body: <p>I have a sheet with a number of pivot tables in Excel, which are controlled by one slicer to set a specific filter. I'm able to abstract the Slicer Items with a VBA function found on the internet:</p>
  <pre><code>Public Function GetSelectedSlicerItems(SlicerName As String) As String
  Dim oSc As SlicerCache
  Dim oSi As SlicerItem
  Dim lCt As Long
  On Error Resume Next
  Application.Volatile
  Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
  If Not oSc Is Nothing Then
      For Each oSi In oSc.SlicerItems
          If oSi.Selected Then
              GetSelectedSlicerItems = GetSelectedSlicerItems &amp; oSi.Name &amp; &quot;, &quot;
              lCt = lCt + 1
          End If
      Next
      If Len(GetSelectedSlicerItems) &gt; 0 Then
          If lCt = oSc.SlicerItems.Count Then
              GetSelectedSlicerItems = &quot;All Items&quot;
          Else
              GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
          End If
      Else
          GetSelectedSlicerItems = &quot;No items selected&quot;
      End If
  Else
      GetSelectedSlicerItems = &quot;No slicer with name '&quot; &amp; SlicerName &amp; &quot;' was found&quot;
  End If
  End Function
  </code></pre>
  <p>Now, what I want to do, is to select the same Items in a different Slicer on another worksheet. So I've found another piece of code here on StackOverflow from @jeffreyweir, that works fine, as long as the array is hardcoded:</p>
  <pre><code>Sub Set_VfMSlicer()
  
  Dim slr As Slicer
  Dim sc As SlicerCache
  Dim si As SlicerItem
  Dim i As Long
  Dim vItem As Variant
  Dim vSelection As Variant
  
  Set sc = ActiveWorkbook.SlicerCaches(&quot;Slicer_Afdeling&quot;)
  'Set sc = slr.SlicerCache
  
  vSelection = Array(&quot;DevOPs&quot;, &quot;Functional Support&quot;, &quot;Technical Support&quot;)
  
  For Each pt In sc.PivotTables
      pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
  Next pt
  
  With sc
  
      'At least one item must remain visible in the Slicer at all times, so make the first
      'item visible, and at the end of the routine, check if it actually  *should* be visible
      .SlicerItems(1).Selected = True
  
      'Hide any other items that aren't already hidden.
      'Note that it is far quicker to check the status than to change it.
      ' So only hide each item if it isn't already hidden
      For i = 2 To .SlicerItems.Count
          If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
      Next i
  
      'Make the PivotItems of interest visible
      On Error Resume Next 'In case one of the items isn't found
      For Each vItem In vSelection
          .SlicerItems(vItem).Selected = True
      Next vItem
      
      
      On Error GoTo 0
  
      'Hide the first PivotItem, unless it is one of the countries of interest
      On Error Resume Next
      If InStr(UCase(Join(vSelection, &quot;|&quot;)), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
      If Err.Number &lt;&gt; 0 Then
          .ClearAllFilters
          MsgBox Title:=&quot;No Items Found&quot;, Prompt:=&quot;None of the desired items was found in the Slicer, so I have cleared the filter&quot;
      End If
      On Error GoTo 0
  End With
  
  
  For Each pt In sc.PivotTables
      pt.ManualUpdate = False
  Next pt
  
  End Sub
  </code></pre>
  <p>I've tried to replace the <code>vSelection = Array(&quot;DevOPs&quot;, &quot;Functional Support&quot;, &quot;Technical Support&quot;)</code></p>
  <p>with: <code>str = GetSelectedSlicerItems(&quot;Slicer_Afdeling1&quot;) vSelection = Split(str, &quot;,&quot;)</code></p>
  <p>which works, as long as only one item is selected in the source slicer. I want to be able to select multiple items as well. How can I fix this? The weird thing is that if a insert a msbBox in the loop to make the right items visible, it gives exactly the right items, which are also available in the slicer.</p>
  <p>ps: the pivot tables are on different worksheets as well, but have the same range as source.</p>