使用阵列过滤枢轴字段

Question

我正在尝试使用每个循环嵌套的一些嵌套将枢轴字段过滤到在数组中保持的值。我要过滤的枢轴字段包含会计周期 - 我想保持可见的会计周期位于单独的纸 - 日期控制表上的一系列单元格中。我拥有的代码如下：

Sub updateMSFPivotCharts()
Dim PivotYear As String, PvtTable As PivotTable, PivotPeriodsRange As Range, DateLastRow As String, PivotPeriods As Variant, ReportPeriodPF As PivotField, YearPF As PivotField, Pi As PivotItem
Dim LboundReportPeriodPF As String, PivotPeriodItem As Variant
PivotYear = Worksheets("Date Control Sheet").Range("A2").Value
DateLastRow = Worksheets("Date Control Sheet").Range("B2").End(xlDown).Row
Set PivotPeriodsRange = Worksheets("Date Control Sheet").Range("B2:B" & DateLastRow)
PivotPeriods = PivotPeriodsRange.Value

'Update Topline Performance Pivot
Call AdjustPivotDataRange("MSF Topline Performance", "1", "A9", "BI", strLastRow)
Set PvtTable = Worksheets("MSF Topline Performance").PivotTables("PivotTable1")
Set YearPF = Worksheets("MSF Topline Performance").PivotTables("PivotTable1").PivotFields("Year")
Set ReportPeriodPF = Worksheets("MSF Topline Performance").PivotTables("PivotTable1").PivotFields("Report Period")
'Filter Pivot to Date Ranges expressed on Date Control Sheet
With PvtTable
    'Update Pivot Table Field Filtering
    YearPF.ClearAllFilters
    ReportPeriodPF.ClearAllFilters
    YearPF.CurrentPage = PivotYear
End With
With ReportPeriodPF
    For Each PivotPeriodItem In PivotPeriods
        For Each Pi In ReportPeriodPF.PivotItems
            If Pi = PivotPeriodItem Then GoTo Skipstep Else Pi.Visible = False
        Next Pi
Skipstep:
    Pi.Visible = True
    Next PivotPeriodItem
End With

我遇到的问题是，当我匹配PI与Pivotperioditem匹配并退出循环时，PI在开始时恢复了pivotperioditem，然后将Pivotperioditem移至下一个项目中，因此PI等于先前的Pivotperioditem再次，当我希望它显示时。

有人对如何重新编写该问题的代码有任何建议吗？非常感谢提前提供的任何帮助。

安德鲁

Answer 1

而不是

With ReportPeriodPF
    For Each PivotPeriodItem In PivotPeriods
        For Each Pi In ReportPeriodPF.PivotItems
            If Pi = PivotPeriodItem Then GoTo Skipstep Else Pi.Visible = False
        Next Pi
Skipstep:
    Pi.Visible = True
    Next PivotPeriodItem
End With

用

替换

With ReportPeriodPF
    For Each Pi In ReportPeriodPF.PivotItems
        If WorksheetFunction.CountIf(PivotPeriodsRange, Pi) > 0 Then
            Pi.Visible = True
        Else
            Pi.Visible = False
        End If
    Next Pi
End With

一个循环，只使用CountIf来测试范围内的范围而不是该范围内的单个项目。