多选项数据透视表筛选同步：VBA实现两透视表筛选匹配

问题核心 #

同一个工作表里的PivotTable1和PivotTable2共享“Area”筛选字段，需要实现：当PivotTable1的筛选（单选项、多选项、全选）变化时，自动同步PivotTable2的筛选状态。原代码仅支持单选项，手动指定数组能运行，但循环收集隐藏项时触发“无法获取PivotField类的PivotItems属性”错误——原因是数据透视表可能包含未启用的项（数据源中曾存在但已删除的项，这类项不可操作），直接循环所有PivotItems会报错。

解决代码（推荐，Excel 2010+） #

把以下代码粘贴到包含两个透视表的工作表代码模块（右键工作表标签→查看代码→粘贴）：

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ' 仅响应PivotTable1的筛选变化，避免循环触发事件
    If Target.Name <> "PivotTable1" Then Exit Sub
    
    Dim pt1 As PivotTable, pt2 As PivotTable
    Dim pf1 As PivotField, pf2 As PivotField
    Dim visibleItems As Variant
    
    Set pt1 = Target
    Set pt2 = Me.PivotTables("PivotTable2")
    Set pf1 = pt1.PivotFields("Area")
    Set pf2 = pt2.PivotFields("Area")
    
    ' 禁用事件，防止同步时重复触发更新
    Application.EnableEvents = False
    
    On Error GoTo ErrorHandler ' 捕获错误，确保事件能恢复
    
    ' 处理全选状态
    If pf1.VisibleItems.Count = pf1.PivotItems.Count Then
        pf2.ClearAllFilters
    Else
        ' 获取PivotTable1的可见项数组，跳过未启用的项
        visibleItems = pf1.VisibleItemsList
        ' 直接同步到PivotTable2
        pf2.VisibleItemsList = visibleItems
    End If
    
ExitSub:
    Application.EnableEvents = True
    Exit Sub
    
ErrorHandler:
    MsgBox "同步筛选出错：" & Err.Description, vbExclamation
    Resume ExitSub
End Sub

兼容旧版本Excel的循环方案（Excel 2007及更早） #

如果使用Excel 2007或更早版本，VisibleItemsList属性不可用，可改用以下循环逻辑（替换上述代码中Else块的内容）：

Else
    ' 先获取PivotTable1的可见项列表
    Dim visibleList As Collection
    Set visibleList = New Collection
    Dim pi1 As PivotItem
    For Each pi1 In pf1.PivotItems
        If pi1.Visible And pi1.Enabled Then
            visibleList.Add pi1.Name
        End If
    Next pi1
    
    ' 同步到PivotTable2
    pf2.ClearAllFilters
    Dim pi2 As PivotItem
    For Each pi2 In pf2.PivotItems
        If pi2.Enabled Then
            ' 判断当前项是否在可见列表中
            pi2.Visible = False
            On Error Resume Next
            visibleList.Item(pi2.Name)
            If Err.Number = 0 Then pi2.Visible = True
            On Error GoTo ErrorHandler
        End If
    Next pi2
End If

关键注意事项 #

• 确保两个透视表的筛选字段名称完全一致（此处为“Area”）
• 代码必须放在对应工作表的代码模块中，不能放在标准模块
• 禁用事件是为了避免同步PivotTable2时再次触发PivotTableUpdate事件，造成循环

内容的提问来源于stack exchange，提问作者seaside_escape