数据透视表选择多个标签_一次选择即可更改所有数据透视表

数据透视表选择多个标签

There is a new sample file on the Contextures website, with a macro to change all pivot tables with one selection, when you change a report filter in one pivot table.

Contextures网站上有一个新的示例文件，其中包含一个宏，当您在一个数据透视表中更改报表过滤器时，可以通过一个选择更改所有数据透视表。

更改所有数据透视表 (Change All Pivot Tables)

In the sample workbook, if you change the "Item" Report Filter in one pivot table, all the other pivot tables with an "Item" filter will change.

在示例工作簿中，如果您在一个数据透视表中更改了“项目” 报表过滤器 ，则所有其他带有“项目”过滤器的数据透视表都将更改。

They get the same report filter settings that were in the pivot table that you changed.

他们获得的数据过滤器设置与更改后的数据透视表中的设置相同。

选择多个项目 (Select Multiple Items)

In this version of the sample file, the "Select Multiple Items" setting is also changed, to match the setting that is in the pivot table that you changed.

在此版本的示例文件中，“选择多个项目”设置也已更改，以匹配您更改的数据透视表中的设置。

In the screen shot below, the Item field has the "Select Multiple Items" setting turned off. If any other pivot tables in the workbook have an "Items" filter, the "Select Multiple Items" setting for those fields will also change.

在下面的屏幕快照中，“项目”字段关闭了“选择多个项目”设置。 如果工作簿中的任何其他数据透视表都具有“项目”过滤器，则这些字段的“选择多个项目”设置也会更改。

这个怎么运作 (How It Works)

The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.

多个数据透视表过滤可与事件编程一起使用。 每个工作表上都有 Worksheet_PivotTableUpdate 代码 ，当该工作表上的任何数据透视表发生更改或刷新时，它就会运行。

For each report filter field, the code checks for the Select Multiple Items setting, to change all Pivot Tables with the same report filter field.

对于每个报表过滤器字段，代码都会检查“选择多个项目”设置，以更改具有相同报表过滤器字段的所有数据透视表。

The code loops through all the worksheets in the workbook, and loops through each pivot table on each sheet.

该代码循环遍历工作簿中的所有工作表，并循环遍历每个工作表上的每个数据透视表。

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
  bMI = pfMain.EnableMultiplePageItems
  For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
      If ws.Name & "_" & pt <> _
          wsMain.Name & "_" & ptMain Then
        pt.ManualUpdate = True
        Set pf = pt.PivotFields(pfMain.Name)
          bMI = pfMain.EnableMultiplePageItems
          With pf
            .ClearAllFilters
            Select Case bMI
              Case False
                .CurrentPage _
                  = pfMain.CurrentPage.Value
              Case True
                .CurrentPage = "(All)"
                For Each pi In pfMain.PivotItems
                  .PivotItems(pi.Name).Visible _
                    = pi.Visible
                Next pi
                .EnableMultiplePageItems = bMI
            End Select
          End With
          bMI = False
        Set pf = Nothing
        pt.ManualUpdate = False
      End If
    Next pt
  Next ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

下载样本文件 (Download the Sample File)

To test the Change All Pivot Tables code, you can download the sample file from the Contextures website.

若要测试“更改​​所有数据透视表”代码，可以从Contextures网站下载示例文件。

On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 - Change All Page Fields with Multiple Selection Settings.

在“ 示例Excel文件”页上的“数据透视表”部分中，查找“ PT0025-更改具有多个选择设置的所有页面字段” 。

The file will work in Excel 2007 or later, if you enable macros.

如果启用宏，该文件将在Excel 2007或更高版本中工作。

观看视频 (Watch the Video)

To see the steps for copying the code into your worksheet, and an explanation of how the code works, watch this short video.

要查看将代码复制到工作表中的步骤以及对代码工作原理的说明，请观看此简短视频。

演示地址

翻译自: https://contexturesblog.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/

数据透视表选择多个标签