Diagnosing/Resolving SlicerCache Issue

[wroze27]

I have the following code which is meant to loop through 3 different slicers I have. The end goal is to create an individual sheet for each possible option using the three slicers and to place them in folders also created by the code.

For reasons I do not understand, I keep getting "Invalid procedure call or arguement" error on line "Set s = ActiveWorkbook.SlicerCaches("OutSalesPersonName 6")" I have double checked and I'm confident on the name of the slicer. Can someone please help me understand what I am doing wrong?

Sub CreateFolders()
  Dim s As SlicerCache
  Dim c As SlicerCache
  Dim m As SlicerCache
  Dim sItem As SlicerItem
  Dim cItem As SlicerItem
  Dim mItem As SlicerItem
  Dim sPath As String
  Dim cPath As String
  Dim mPath As String

  ' Set the slicer cache for each slicer
  Set s = ActiveWorkbook.SlicerCaches("OutSalesPersonName 6")
  Set c = ActiveWorkbook.SlicerCaches("Customer Name 3")
  Set m = ActiveWorkbook.SlicerCaches("Mfr 5")

  ' Loop through each item in the "OutSalesPersonName" slicer
  For Each sItem In s.SlicerItems
    ' If the slicer item is selected, create a folder with its name
    If sItem.Selected = True Then
      sPath = "C:\Users\wrozelle\Desktop\Reports\OS Reviews\" & sItem.Name
      MkDir sPath
      
      ' Loop through each item in the "Customer Name" slicer
      For Each cItem In c.SlicerItems
        ' If the slicer item is selected, create a folder with its name within the previous folder
        If cItem.Selected = True Then
          cPath = sPath & "\" & cItem.Name
          MkDir cPath
          
          ' Loop through each item in the "mfr" slicer
          For Each mItem In m.SlicerItems
            ' If the slicer item is selected, save a copy of "SPA Utilization" to the folder
            If mItem.Selected = True Then
              mPath = cPath & "\" & mItem.Name & ".xlsx"
              ActiveWorkbook.Sheets("SPA Utilization").Copy
              ActiveWorkbook.SaveAs Filename:=mPath
              ActiveWorkbook.Close False
            End If
          Next mItem
        End If
      Next cItem
    End If
  Next sItem
End Sub

 

[Georgiboy]

Does it work if you use:

Set s = ActiveWorkbook.SlicerCaches("Slicer_OutSalesPersonName 6")

 

[wroze27]

Just tried it and unfortunately no it didn't

 

[RoryA]

I'd expect those to be prefixed with Slicer_ and have any spaces replaced with underscores.

 

[wroze27]

I'd expect those to be prefixed with Slicer_ and have any spaces replaced with underscores.

I added slicer and used underscores instead of spaces but it didn't work

 

[Georgiboy]

When you right click on the slicer and select slicer settings, have a look at the section 'Name to use in formulas:'

 

[wroze27]

Now, im having a new issue: now im getting application-defined or object-defined error on:

For Each sItem In s.SlicerItems