在vba中设置多个值作为数据透视表过滤条件

Question

我试图使用VBA宏来自动更新具有特定过滤值的不同工作簿中的数据透视表。有大约10个值，我感兴趣的看，但开始我想能够在同一时间查看两个。我使用变量来动态引用要根据月和周数打开的工作簿。有问题的那条线是最下面的那条。

Dim varMonth As String
Dim varWeek As String
Worksheets("Data").Activate
varMonth = Range("B2").Value
varWeek = Range("B3").Value
Workbooks.Open("filepath" & varMonth & "/" & varMonth & " WK " & varWeek & " D1 Reconciliation.xlsx").Sheets("Pivot").Activate
Sheets("Pivot").PivotTables("D1Pivot").PivotFields("Marketing Referral Type").ClearAllFilters
Sheets("Pivot").PivotTables("D1Pivot").PivotFields("Marketing Referral Type").CurrentPage = "Digital"
Sheets("Pivot").PivotTables("D1Pivot").PivotFields("Appointment Status").ClearAllFilters
Sheets("Pivot").PivotTables("D1Pivot").PivotFields("Appointment Status").CurrentPage = "Showed/Day1" And "No Show"

我还在适应VBA的能力，所以我想知道这是否是一个简单的修复，我只是还没有看到。

Answer 1

尝试遍历数据透视字段中的每个数据透视项，然后检查名称是否为"显示/Day1"或者"No show"。如果没有，则将透视项的Visible属性设置为False…

With Sheets("Pivot").PivotTables("D1Pivot").PivotFields("Appointment Status")
.ClearAllFilters
Dim pi As PivotItem
For Each pi In .PivotItems
If pi.Name <> "Showed/Day1" And pi.Name <> "No Show" Then
pi.Visible = False
End If
Next pi
End With

然而，你的代码可以重写如下…

With Worksheets("Data")
Dim varMonth As String
varMonth = .Range("B2").Value

Dim varWeek As String
varWeek = .Range("B3").Value

End With
Workbooks.Open "filepath" & varMonth & "/" & varMonth & " WK " & varWeek & " D1 Reconciliation.xlsx"
With Sheets("Pivot").PivotTables("D1Pivot")
With .PivotFields("Marketing Referral Type")
.ClearAllFilters
.CurrentPage = "Digital"
End With

With .PivotFields("Appointment Status")
.ClearAllFilters
Dim pi As PivotItem
For Each pi In .PivotItems
If pi.Name <> "Showed/Day1" And pi.Name <> "No Show" Then
pi.Visible = False
End If
Next pi
End With

End With