如何在VBA中从动态字符串生成数组以同步数据透视表筛选器？

问题核心 #

你试图通过拼接字符串生成筛选项列表再转数组，但Array("" & MS & "")的写法会把整个字符串当成单个元素传入数组，导致后续调用.PivotItems(myArray(i))时找不到对应项，触发错误。

正确解决方案 #

没必要绕弯子拼接字符串，直接同步可见性或者正确生成动态数组即可：

方案1：直接同步可见性（最简洁高效） #

跳过数组中转，直接遍历第一个透视表的字段项，同步第二个表对应项的可见状态：

Sub FilterPivotTables()
    Dim pt1 As PivotTable, pt2 As PivotTable
    Dim pf1 As PivotField, pf2 As PivotField
    Dim pi As PivotItem
    
    ' 绑定目标透视表和字段
    Set pt1 = ActiveSheet.PivotTables("PivotTable1")
    Set pt2 = ActiveSheet.PivotTables("PivotTable2")
    Set pf1 = pt1.PivotFields("Area")
    Set pf2 = pt2.PivotFields("Area")
    
    ' 重置第二个表的筛选状态
    pf2.ClearManualFilter
    pf2.EnableMultiplePageItems = True
    
    ' 逐个同步项的可见性
    For Each pi In pf1.PivotItems
        On Error Resume Next ' 兼容两个表项不一致的情况
        pf2.PivotItems(pi.Name).Visible = pi.Visible
        On Error GoTo 0
    Next pi
End Sub

方案2：正确生成动态数组（若需数组中转） #

如果一定要用数组存储筛选项，直接在遍历过程中填充数组，不要拼接字符串：

Sub FilterPivotTables_ArrayVersion()
    Dim pt1 As PivotTable, pt2 As PivotTable
    Dim pf1 As PivotField, pf2 As PivotField
    Dim pi As PivotItem
    Dim hiddenItems() As String
    Dim i As Integer
    
    Set pt1 = ActiveSheet.PivotTables("PivotTable1")
    Set pf1 = pt1.PivotFields("Area")
    
    ' 初始化动态数组
    ReDim hiddenItems(0 To pf1.PivotItems.Count - 1)
    i = 0
    
    ' 收集需要隐藏的项
    For Each pi In pf1.PivotItems
        If Not pi.Visible Then
            hiddenItems(i) = pi.Name
            i = i + 1
        End If
    Next pi
    ' 调整数组到实际元素数量
    ReDim Preserve hiddenItems(0 To i - 1)
    
    ' 应用筛选到第二个透视表
    Set pt2 = ActiveSheet.PivotTables("PivotTable2")
    Set pf2 = pt2.PivotFields("Area")
    
    With pf2
        .ClearManualFilter
        .EnableMultiplePageItems = True
        ' 先将所有项设为可见，再隐藏指定项
        For Each pi In .PivotItems
            pi.Visible = True
        Next pi
        For i = LBound(hiddenItems) To UBound(hiddenItems)
            On Error Resume Next
            .PivotItems(hiddenItems(i)).Visible = False
            On Error GoTo 0
        Next i
    End With
End Sub

错误原因说明 #

你之前生成的MS是形如"Red Zone", "Blue Zone"的字符串，但Array("" & MS & "")会生成一个仅含单个元素的数组——这个元素就是整个长字符串，而非两个独立的项。用这个数组去查找PivotItem时，自然找不到名为"Red Zone", "Blue Zone"的项，因此触发报错。

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