vba 如何在单个工作表中刷新切片器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22590258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you refresh Slicers in a single sheet?
提问by JJ Moore
I'd like to clear the filters of all slicers within a sheet but leave the other slicers "un-refreshed".
我想清除工作表中所有切片器的过滤器，但让其他切片器“未刷新”。
I have seen the following code that refreshes all slicers within the workbook but can't get it to work solely on a particular sheet only.
我已经看到以下代码刷新工作簿中的所有切片器，但无法让它仅在特定工作表上工作。
Dim slcr As SlicerCache
For Each slcr In ActiveWorkbook.SlicerCaches
slcr.ClearManualFilter
Next slcr
Any help gratefully received!
任何帮助表示感谢！
Thanks
谢谢
回答by Cor_Blimey
After a bit of playing around and testing (never used slicers through VBA before) I worked out the below.
经过一番尝试和测试（以前从未通过 VBA 使用切片器），我得出了以下内容。
However, be advised that a slicercache can have slicers on different worksheets. A slicer is linked to a slicer cache which in turn is linked to a data source (e.g. a pivot table). If any slicer is changed, then the slicer cache is changed which changes the pivot table as well. You cannot have 1 slicer with a filter on and another slicer linked to the same pivot table with a filter off, as the pivot table is updated by a slicer's filters. It would't make sense. ;-)
但是，请注意 slicercache 可以在不同的工作表上具有切片器。切片器链接到切片器缓存，而切片器缓存又链接到数据源（例如数据透视表）。如果更改了任何切片器，则切片器缓存也会更改，这也会更改数据透视表。您不能让 1 个切片器打开过滤器，而另一个切片器链接到同一个数据透视表而关闭过滤器，因为数据透视表由切片器的过滤器更新。这没有意义。;-)
What does make sense is having multiple slicers and multiple pivot tables - using the below will indeed clear the filters for one slicer / cache / pivot but not change any others. This is the only way it could work so hopefully that was what you wanted anyway!!
有多个切片器和多个数据透视表才有意义 - 使用以下确实会清除一个切片器/缓存/数据透视表的过滤器，但不会更改任何其他过滤器。这是它可以工作的唯一方法，所以希望这就是你想要的！！
Sub test()
RefreshSlicersOnWorksheet ActiveSheet
End Sub
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
sc.ClearManualFilter
Exit For 'unnecessary to check the other slicers of the slicer cache
End If
Next slice
Next sc
End If
End Sub
