删除连接到同一工作簿中分离源数据的切片器

Question

我真的不知道如何写出我的问题，所以你会理解，但我会尽我最大的努力。我希望你能得到我想要的基础。

我有一个工作簿，其中包含两个独立的表，两个仪表板，每个表一个。连接到Table1和Table2的多个枢轴。两个表的标题相同，但它们中的数据不同。

我的工作表是这样的。

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

表1及其枢轴用于仪表板1，表2用于仪表板2。

因为我想分离这些代码，我有不同的代码来更新仪表板1和2。这段代码清除了仪表板1与图表和切片器，并创建了新的图表和切片器。

我的问题。我从每个仪表板中删除的代码和图表如下所示。

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

上面的代码明显地删除了我工作簿中的所有切片器。即使那些在仪表板2，如果我想更新仪表板1和这我不希望。

我真的不知道如何声明我的代码，所以我只删除那些连接到仪表板1中的表1或仪表板2中的表2的切片器，这样就可以保存在单独的仪表板中。如果有人能在正确的方向上帮助我，我会很高兴，并加深我对excel支点的了解。

编辑-

这是我用来从表1和表2中获取切片程序的代码，我只更改了子名称。因此，对于表2，我从这段代码中获得了切片器，对于表1，除了不同的数据透视表名称之外，它是相同的。

 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

提前谢谢！/D

Answer 1

您可以检查切片分配给哪个枢轴表(但请注意，切片器可以分配给多个枢轴。然后，根据数据透视的名称(或者枢轴的数据源)，您可以决定是否删除Slicer。注意，当从集合中删除某些内容时，始终必须向后循环。也许这段代码可以给你一个提示：

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

Answer 2

如果我理解正确的话，您为“仪表板2”创建了10个切片机--我假设您在某个地方为“仪表板1”做了同样的事情。我建议给切割机起个名字，这样你就能认出它们了。

我已经修改了您的子例程，以便它可以用于两个仪表板，只需将工作表作为参数传递-但是它是完全未经测试的，因为我没有设置您的工作簿。您可以看到，所有10个切片器都是在一个循环中创建的。也许你必须玩topPos/leftPos的计算，但我想你明白了。注意:当删除切片器时，必须访问切片器--切片缓存对象的成员--以检查名称(以前不知道)

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 AddSlicers("Set2", ThisWorkbook.Worksheets("table2"), _
                        ThisWorkbook.Worksheets("Dashboard 2"), _
                        ThisWorkbook.Worksheets("Pivot2Hours"), _
                        ThisWorkbook.Worksheets("Pivot2Cost"))

您提到的运行时错误可能可以解释:您必须在删除SlicerCache之后退出内部循环。我编辑了我的第一个答案。