VBA to select first item in a slicer causes Invalid procedure call or argument|VBA & Macros|Excel Forum|My Online Training Hub

Hello -

I have a macro called "ResetReport" that deletes all slicers on a sheet and then adds back (and formats) four specific slicers to the same sheet. This is essentially a mechanism to "reset" a report to the way it was originally setup. The last step in the procedure connects the new slicers to the table that they will control. Everything up to and including that point works fine.

What I'm trying to do next is de-select all slicer items except the first so that the chart does not show an overwhelming amount of information (i.e., all options from all slicers). The code I've written (some borrowed, some original) causes an error.

Sub ResetReport

  ' The example I'm putting here simplifies things for purposes of this question

  'Call DeleteAllSlicers ' Remove all existing slicers
  'Call AddDefaultSlicers ' Add 2-4 slicers, depending on the active sheet from which this sub is called
  
  ' Note that the slicers created in this routine are given the following names
  ' Slicer #1: ActiveSheet.Name & Chr(32) & "ART"
  ' Slicer #2: ActiveSheet.Name & Chr(32) & "TEAM"
  ' Slicer #3: ActiveSheet.Name & Chr(32) & "PI"
  ' Slicer #4: ActiveSheet.Name & Chr(32) & "SPRINT"

 'Call SetSlicerProperties ' Formats the new slicers (header, caption, position, size, etc.)
 'Call ConnectSlicersToTable ' Connects the slicers to the table on the sheet
 
  Dim strSlicerName As String
  strSlicerName = ActiveSheet.Name & Chr(32) & "ART" ' I'm hard coding this here so you can see what's passed
  Call SliceByIndex(strSlicerName) ' This is the sub that's erroring out

End Sub

Sub SliceByIndex(strSlicerName As String)
Debug.Print Chr(10)
Debug.Print "START SliceByIndex (" & strSlicerName & ")" ' This line prints, so we get this far

  ' Declare variables for this procedure
  Dim sc As SlicerCache
  Dim si As SlicerItem
  Dim lSlicerItemsCount As Long
  Dim n As Long

  MsgBox "The name of the slicer is: " & strSlicerName ' This shows the correct slicer name 

  ' Set variable values
  Set sc = ThisWorkbook.SlicerCaches(strSlicerName) ' This line triggers Err 5: "Invalid procedure call or argument"

  ' Count slicer items
  lSlicerItemsCount = sc.SlicerItems.Count ' We never get this far
  Debug.Print "Number of slicer items: " & lSlicerItemsCount

  With sc

  ' Temporarily suspend PivotCache recalculation
  Debug.Print "Suspend PivotCache recalculation..."
  .PivotTables(1).ManualUpdate = True

  ' Select first slicer item (required)
  Debug.Print "Select first slicer item..."
  .SlicerItems(1).Selected = True

  ' Deselect all otehr slicer items
  For n = 2 To lSlicerItemsCount
  Debug.Print "Deselect slicer item: " & n & "..."
  .SlicerItems(n).Selected = False
  Next n

  ' Resume PivotCache recalculation
  Debug.Print "Resume PivotCache recalculation..."
  .PivotTables(1).ManualUpdate = False

End With

' Skip over error handler
GoTo GOODBYE

ERROR_HANDLER:

' Display error
Msgbox ("SliceByIndex", Err.Number, Err.Description, strSlicerName)

GOODBYE:

debug.print "END: SliceByIndex"
End Sub