would like to get some advice on code I have below.
Getting a run-time error’1004: Application-defined or object-defined error at the code section below.
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…
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)