Strange error during addition of slicer cache

Question

I'm developing an application with multiple pivot tables and slicers.

I try to prepare a template sheet and copy - paste it in order to create multiple analysis.

When I duplicate the sheet, the Slicers will be linked to both original and new pivot tables (belonging to the same SlicerCache), so I need to:

• Unlink original SlicerCache from the new pivot table
• Delete original Slicer from the new sheet
• create new SlicerCache with the same connection settings
• create new Slicer on the new sheet, belonging to the new SlicerCache

My code so far:

Function DuplicateSlicer(PreviousSlicer As Slicer) As Slicer
    Dim NewSlC As SlicerCache
    Dim NewSlicer As Slicer
    Dim DestWorkSheet As Worksheet
    Dim SlCSequence As Integer
    Dim NewSlCName As String

    With PreviousSlicer
        Set DestWorkSheet = .Parent
        .SlicerCache.PivotTables.RemovePivotTable (DestWorkSheet.PivotTables(1))
        SlCSequence = 1
        Do Until GetSlicerCache(DestWorkSheet.Parent, .SlicerCache.Name & SlCSequence) Is Nothing
            SlCSequence = SlCSequence + 1
        Loop
        NewSlCName = .SlicerCache.Name & SlCSequence
        Set NewSlC = DestWorkSheet.Parent.SlicerCaches.Add2(DestWorkSheet.PivotTables(1), _
            .SlicerCache.SourceName, .SlicerCache.Name & SlCSequence)
        Set NewSlicer = NewSlC.Slicers.Add(DestWorkSheet, Caption:=.SlicerCache.SourceName, _
            Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height)

        NewSlicer.SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData

        .Delete
    End With

End Function

My problem is with the line
DestWorkSheet.Parent.SlicerCaches.Add2(DestWorkSheet.PivotTables(1), _ .SlicerCache.SourceName, .SlicerCache.Name & SlCSequence)

According to MSDN help it should work even without specifying name:

The name Excel uses to reference the slicer cache (the value of the SlicerCache.Name property). If omitted, Excel will generate a name. By default, Excel concatenates "Slicer_" with the value of the PivotField.Caption property for slicers with non-OLAP data sources, ... (Replacing any spaces with "_".) If required to make the name unique in the workbook namespace, Excel adds an integer to the end of the generated name. If you specify a name that already exists in the workbook namespace, the Add method will fail.

However even if I use my code as above, or I just omit 3rd parameter, I keep getting

error 1004: The slicer cache already exists.

To make things even more complicated, if I use a variable for name parameter of Slicercaches.Add (NewSlCName = .SlicerCache.Name & SlCSequence) I get different one:

error: 5 "Invalid procedure call or argument"

I really don't have any idea how to fix it.

Update

I've used SlicerCaches.Add2 as that's available from the object tips.
According to another article .Add is deprecated and shouldn't be used.
I've also tried .Add instead of .Add2, it gives the same error.

Answer 1

So far the only approach I could make to work is this:

Create two templates with the same layout and pivot tables, one of them with slicers and the other is without.

To create a new sheet: duplicate the template without slicers, then run below code for creating the slicers in the new sheet:

Sub DuplicateSlicers(NewWorkSheet As Worksheet, SourceWorkSheet As Worksheet)
    Dim SlC As SlicerCache
    Dim sl As Slicer

    For Each SlC In SourceWorkSheet.Parent.SlicerCaches
        For Each sl In SlC.Slicers
            If (sl.Parent Is SourceWorkSheet) Then
                Call DuplicateSlicer(sl, NewWorkSheet)
            End If
        Next sl
    Next SlC
End Sub

Function DuplicateSlicer(PreviousSlicer As Slicer, NewSheet As Worksheet) As Slicer
    Dim NewSlC As SlicerCache
    Dim NewSlicer As Slicer

    If PreviousSlicer Is Nothing Then
        Set DuplicateSlicer = Nothing
        Exit Function
    End If

    On Error GoTo ErrLabel

    With PreviousSlicer
        Set NewSlC = NewSheet.Parent.SlicerCaches.Add2(NewSheet.PivotTables(1), _
            .SlicerCache.SourceName)
        Set NewSlicer = NewSlC.Slicers.Add(NewSheet, Caption:=.Caption, Top:=.Top, Left:=.Left, _
            Width:=.Width, Height:=.Height)
    End With
    NewSlicer.SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
    Set DuplicateSlicer = NewSlicer

    Exit Function

ErrLabel:
    Debug.Print PreviousSlicer.Caption & " - " & Err.Number & ": " & Err.Description
    Err.Clear

End Function