vba Excel 2007 跨多个数据透视表过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1423753/
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
Excel 2007 Filtering across multiple Pivot Tables
提问by John Sullivan
In Excel 2007, I have a single dataset in the form of an Excel Table from which I want to make multiple pivot tables and pivot charts. I would like to be able to synchronize the 'report filter' field across all of the pivot tables and charts simultaneously, so that by filtering, (for example) the US_Region field to 'Pacific Northwest' on one of the pivot tables would apply that filter to all of the pivot tables and charts in the workbook (which share a single dataset). Is there a way to do this without VBA and, if there is not, what would be the most flexible/least klugy way to do it in VBA?
在 Excel 2007 中，我有一个 Excel 表格形式的单个数据集，我想从中制作多个数据透视表和数据透视图。我希望能够同时在所有数据透视表和图表中同步“报告过滤器”字段，以便通过过滤（例如）在其中一个数据透视表上将 US_Region 字段应用到“太平洋西北地区”筛选到工作簿中的所有数据透视表和图表（共享单个数据集）。有没有办法在没有 VBA 的情况下做到这一点，如果没有，在 VBA 中最灵活/最不笨拙的方法是什么？
Thanks
谢谢
回答by dendarii
Create a procedure to change all the page fields. This one loops through all the worksheets in the workbook and if the worksheet has a pivot table, it changes the page field to match the page field on the pivot table passed to the sub.
创建一个过程来更改所有页面字段。这个循环遍历工作簿中的所有工作表，如果工作表有一个数据透视表，它会更改页面字段以匹配传递给子的数据透视表上的页面字段。
Sub ChangePage(pt As PivotTable)
Dim strPageValue As String
Dim wks As Worksheet
Dim ptUpdate As PivotTable
Application.ScreenUpdating = False
Application.EnableEvents = False
strPageValue = pt.PivotFields("Area").CurrentPage
For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
Set ptUpdate = wks.PivotTables(1)
If Err.Number = 0 Then
On Error GoTo 0
wks.PivotTables(1).PivotFields("Area").ClearAllFilters
wks.PivotTables(1).PivotFields("Area").CurrentPage = strPageValue
End If
Err.Clear
Next wks
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Then place a call to this procedure in the worksheet code itself and pass the pivot table you changed:
然后在工作表代码本身中调用此过程并传递您更改的数据透视表：
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call ChangePage(Target)
End Sub
Change the name of the pivot field from "Area" to the page field you need, for example "US_Region".
将数据透视字段的名称从“Area”更改为您需要的页面字段，例如“US_Region”。
This may not be suitable if people are frequently changing the structure of your pivot tables in other ways, as it will trigger each time the pivot table is changed, including when it is refreshed. It will also give an error if users remove the specified page field.
如果人们经常以其他方式更改数据透视表的结构，这可能不合适，因为每次更改数据透视表时都会触发，包括刷新时。如果用户删除指定的页面字段，它也会出错。
If the pivot tables are static though, this might do.
如果数据透视表是静态的，这可能会。
