Delete slicers connected to seperate source data in same workbook

Question

I really dont know how to write out my question for this so u will understand but i will do my best. I hope u get the base of what i want.

I have 1 workbook that contains 2 seperate tables, 2 dashboards, one for each table. Multiple pivottables that are connectet to Table1 and Table2. Both tables look the same with the same headers but the data in them differs.

My workbook sheets looks like this.

    Dashboard 1| Dashboard 2| Table 1| Table2| Pivot1Hours| Pivot2Hours|  Pivot1cost| Pivot2cost.

Table 1 and its pivottables make for dashboard 1 and Table 2 for dashboard 2.

Beacuse i want to seperate these i have diffrent code to uppdate dashboard 1 and 2. This code clears Dashboard 1 from graphs and slicers and create new graphs and slicers.

MY PROBLEM. The code i have delete slicercach and graphs from each dashboard looks like this.

Dim ws As Worksheet
Dim Chrtobj As ChartObject
Dim SC As SlicerCache
 Set ws = ThisWorkbook.Sheets("The dashboard i want to use")
 If Not ws.ChartObjects.Count = 0 Then ws.ChartObjects.Delete
 For Each SC In ActiveWorkbook.SlicerCaches
    SC.Delete
    Next SC

This code above obviosly deletes all the slicers i have in my workbook. even those in dashboard 2 if i want to uppdate dashboard 1 and this i do not wish.

I really dont know how to declare my code so i only delete those slicers connected to table 1 in dashboard 1 or table 2 in dashboard 2 and so saves those in seperate dashboard. IF someone can help me in the right direction i would be so happy and deepen my knowledge of excel pivotcach.

Edit-

This is the code i use to fetch slicers from table 1 and 2, i have only changed the sub name. So for table 2 i get the slicers from this code, and for table 1 its the same except diffrent worksheet names for pivottables.

 Sub dografer2()

Dim slizename As String
Dim rensaantal As Long
Dim i As Long
Dim ws As Worksheet
Dim Chrtobj As ChartObject
Dim sc As SlicerCache
Dim slizern As String
Dim col As Integer
Dim position As Integer
Dim topp As Integer


'Tar bort grafer
Set ws = ThisWorkbook.Sheets("Dashboard 2")
'~~> Check if there are any chartobjects in the sheet
If Not ws.ChartObjects.Count = 0 Then ws.ChartObjects.Delete
'remove slicers/slicercach

For Each sc In ActiveWorkbook.SlicerCaches
    sc.Delete
    Next sc

'Create graphs
Call HeltidsGraf_2
Call TimmarGraf_2
Call Totalgraf2_2
Call HeltidsGraf2_2
Call KostnadsGraf_2
Call Totalgraf_2



position = 25
topp = 150 + 300 + 300 + 300 - 600

   For col = 1 To 3

    'slicername
      slizern = Worksheets("Table2").Cells(2, col).Value
       Debug.Print (slizern)


    'Create slicers
    ActiveWorkbook.SlicerCaches.Add(Worksheets("Pivot2Hours").PivotTables(1), slizern) _
    .Slicers.Add Worksheets("Dashboard 2"), , slizern, slizern, topp, position, 210, 300
    Debug.Print (ThisWorkbook.SlicerCaches(col).Name)
     slizename = ThisWorkbook.SlicerCaches(col).Name

  ActiveWorkbook.SlicerCaches(slizename).PivotTables.AddPivotTable (Worksheets("Pivot2Cost").PivotTables(1))

    position = position + 210
Next col

position = 25
topp = 150 + 300 + 300
For col = 4 To 6

   'slicername
    slizern = Worksheets("Table2").Cells(2, col).Value
    Debug.Print (slizern)


   'Create slicers
    ActiveWorkbook.SlicerCaches.Add(Worksheets("Pivot2Hours").PivotTables(1), slizern) _
    .Slicers.Add Worksheets("Dashboard 2"), , slizern, slizern, topp, position, 210, 300
    Debug.Print (ThisWorkbook.SlicerCaches(col).Name)
    slizename = ThisWorkbook.SlicerCaches(col).Name

  ActiveWorkbook.SlicerCaches(slizename).PivotTables.AddPivotTable (Worksheets("Pivot2Cost").PivotTables(1))



    position = position + 210
Next col

position = 25
topp = 150 + 300
For col = 7 To 9
     'slicername
    slizern = Worksheets("Table2").Cells(2, col).Value
    Debug.Print (slizern)


   'Create slicers
    ActiveWorkbook.SlicerCaches.Add(Worksheets("Pivot2Hours").PivotTables(1), slizern) _
    .Slicers.Add Worksheets("Dashboard 2"), , slizern, slizern, topp, position, 210, 300
    Debug.Print (ThisWorkbook.SlicerCaches(col).Name)
    slizename = ThisWorkbook.SlicerCaches(col).Name

  ActiveWorkbook.SlicerCaches(slizename).PivotTables.AddPivotTable (Worksheets("Pivot2Cost").PivotTables(1))

    position = position + 210
 Next col

position = 25
topp = 150
'topp = 150 + 300 + 300 + 300


col = 10
       'Slicername
     slizern = Worksheets("Table2").Cells(2, col).Value
    Debug.Print (slizern)

    'Create slicers
       ActiveWorkbook.SlicerCaches.Add(Worksheets("Pivot2Hours").PivotTables  (1), slizern) _
      .Slicers.Add Worksheets("Dashboard 2"), , slizern, slizern, topp, position, 210, 300
     Debug.Print (ThisWorkbook.SlicerCaches(col).Name)
    slizename = ThisWorkbook.SlicerCaches(col).Name

    ActiveWorkbook.SlicerCaches(slizename).PivotTables.AddPivotTable (Worksheets("Pivot2Cost").PivotTables(1))



End Sub

Thanks in advance! /D

Answer 1

If I understand correctly, you created 10 slicers for the "Dashboard 2" - and I assume you do the same somewhere for "Dashboard 1". I would suggest to name the slicers to that you can identify them.

I have modified your Subroutine so that it works for both dashboards, just pass the worksheets as parameters - but it's completely untested as I don't have the setup of your workbook. You can see that all 10 Slicers are created within one loop. Maybe you have to play with the topPos/leftPos calculation but I think you get the idea. Note when deleting the slicers, you have to access the slicers-member of the slicerCache-Object to check the name (didn't know that before)

Sub AddSlicers(postFix As String, tableWs As Worksheet, dashboardWs As Worksheet, hoursWs As Worksheet, costWs As Worksheet)

Dim sc As SlicerCache
For Each sc In ActiveWorkbook.SlicerCaches
    If Right(sc.Slicers(1).Name, Len(postFix)) = postFix Then sc.Delete
Next sc

Dim col As Long, topPos As Double, leftPos As Double
topPos = 150
leftPos = 25

For col = 1 To 10

    Dim slicerFieldName As String, slicerName As String
    slicerFieldName = tableWs.Cells(2, col).Value
    slicerName = tableWs.Cells(2, col).Value & postFix

    ActiveWorkbook.SlicerCaches.Add(hoursWs.PivotTables(1), slicerFieldName) _
        .Slicers.Add dashboardWs, , slicerName, slicerFieldName, topPos, leftPos, 210, 300

    Call ActiveWorkbook.SlicerCaches(slicerName).PivotTables.AddPivotTable(costWs.PivotTables(1))

    If col Mod 3 = 0 Then
        topPos = topPos + 300
        leftPos = 25
    Else
        leftPos = leftPos + 210
    End If

Next col

End Sub

Call it with

    Call AddSlicers("Set2", ThisWorkbook.Worksheets("table2"), _
                        ThisWorkbook.Worksheets("Dashboard 2"), _
                        ThisWorkbook.Worksheets("Pivot2Hours"), _
                        ThisWorkbook.Worksheets("Pivot2Cost"))

The runtime error you mentioned maybe can be explained: You have to exit the inner loop after deleting the SlicerCache. I've edited my first answer.

Answer 2

You can check to which pivot table the slice is assigned (but note that a slicer can be assigned to more than one pivot. Then maybe depending on the name of the Pivot (or of the data source of the pivot) you can decide if to delete or not the Slicer. Note that when deleting something from a collection, you always have to loop backwards. Maybe this piece of code can give you a hint:

Sub DeleteSlicerCache()

Dim si As Long, s As SlicerCache
For si = ThisWorkbook.SlicerCaches.Count To 1 Step -1
    Set s = ThisWorkbook.SlicerCaches(si)

    Debug.Print "Slicer: ", si, s.Name
    Dim pi As Long
    For pi = 1 To s.PivotTables.Count
        Dim p As PivotTable
        Set p = s.PivotTables(pi)
        Debug.Print "Pivot:", si & "." & pi & p.Name, p.SourceData
        If p.Name = "PivotTable1" Then ' <== Enter your condition here
            ThisWorkbook.SlicerCaches(si).Delete
            Exit for
        End If
    Next pi
Next si
End Sub