(Solved) – VBA – Slicercache Addpivottable Error-1004

(solved)-–-vba-–-slicercache-addpivottable-error-1004

would like to get some advice on code I have below.

Problem:
Getting a run-time error’1004: Application-defined or object-defined error at the code section below.

ActiveWorkbook.SlicerCaches(sl.Name).PivotTables.AddPivotTable pt

where “sl” is the slicercache name, “pt” is a PivotTable object so directly referencing the object

I’ve also done different references for the above, but still failed miserably like…

ActiveWorkbook.SlicerCaches(sl.Name).PivotTables.AddPivotTable Worksheet("PivotSheet").PivotTables(pt.name)

Purpose:
Trying to connect additional pivot tables (total of 30) to an existing slicercache (total of 5) where these pivot tables are instantiated and created by a Class object (“PivTab”).

What’s been done:

  • Can generate pivot tables and pivot charts by looping through and instancing a new class of the aforementioned.
  • Can generate the required slicercaches and slicers (5) and connect to one pivot table

(1) Code function that loops the required info to generate pivot table/chart/slicer

    For i = 2 To LastRow <- each row contains specs to build pivot table, pivot chart, slicer
        Dim oPT As New PivTab
        Dim oPC As New PivCht
        Dim oPS As New PivSlicer

        With PropSheet
            Set rLoop = .Range(.Cells(i, 1), .Cells(i, LastCol))

            oPT.ReadData PropSheet, rLoop
            oPT.SetupTable PivSheet, PivRange

            oPC.ReadData PropSheet, rLoop
            oPC.MakeCht PivSheet, oPT.ID

            oPS.ReadData PropSheet, rLoop
            oPS.SetSlicer PivSheet, oPT.PivTable, NameFieldDict

        End With
    Next

(2) Extract of "PivSlicer" class containing function "SetSlicer" to set connect pivot table to slicercache

Public Function SetSlicer(ByVal PivSheet As Worksheet, pt As Object, dictNameField As Dictionary)
    Dim k, j As Variant
    Dim sl As SlicerCache
    Dim pvts As SlicerPivotTables

    If iCounter = 0 Then
        For Each k In dictNameField.Keys
            Set SCache = ActiveWorkbook.SlicerCaches.Add2(pt, CStr(dictNameField(k)), CStr(Replace(k, " ", "")))
        Next

        Call CreateFormatSlicer(PivSheet, ActiveWorkbook.Worksheets("Chart")) <- creates slicer

        For Each sl In ActiveWorkbook.SlicerCaches
            For Each j In ReqDict.Keys
                If InStr(1, j, sl.Name, vbBinaryCompare) > 0 And CStr(ReqDict(j)) = "Yes" Then
                    ActiveWorkbook.SlicerCaches(sl.Name).PivotTables.AddPivotTable pt
                End If
            Next
        Next
    End If

Much thanks and hope someone can help me through this as I've been stuck on this back-and-forth for days (looked through other sources and still can't find a starting approach to why this is happening)

Leave a Reply

Your email address will not be published. Required fields are marked *