VBA清除所有切片器并刷新数据透视表Excel 2013

Question

我正在寻找VBA清除所有切片和刷新数据透视表Excel 2013，然而，它打破了"ClearAllFilters“。我研究的每一件事都建议使用它。是否有更好的方法来刷新切片并显示所有结果。此外，还希望包括数据模型的刷新和数据透视表。

Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)

Dim sc As SlicerCache
Dim scs As SlicerCaches
Dim slice As Slicer

Set scs = ws.Parent.SlicerCaches

If Not scs Is Nothing Then
    For Each sc In scs
        For Each slice In sc.slicers
            If slice.Shape.Parent Is ws Then
                slice.ClearAllFilters
                Exit For 'unnecessary to check the other slicers of the slicer cache
            End If
        Next slice
    Next sc
End If

结束子对象

我试过你的建议，然而，仍然有问题的ClearAllFilters。

    Sub Reset()
Dim pt As PivotTable

 Application.ScreenUpdating = False
 ActiveWorkbook.Model.Refresh
    RefreshSlicersOnWorksheet ActiveSheet
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
Application.ScreenUpdating = True
End Sub
Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)
Dim pt As PivotTable
Dim slice As Slicer

For Each pt In ws.PivotTables
    For Each slice In pt.Slicers
        slice.SlicerCache.ClearAllFilters
    Next slice
    pt.PivotCache.Refresh
Next pt
End Sub

Answer 1

ClearAllFilters方法应用于SlicerCache对象，而不是切割机对象。

更改：

slice.ClearAllFilters

至：

sc.ClearAllFilters

编辑：，您也可以从工作表中的PivotTables集合开始，然后从那里访问切片器。然后，您可以刷新透视表，如下所示：

Dim pt As PivotTable
Dim slice As Slicer

For Each pt In ws.PivotTables
    For Each slice In pt.Slicers
        slice.SlicerCache.ClearAllFilters
    Next slice
    pt.PivotCache.Refresh
Next pt