时间戳

首页 > 解决方案 > VBA更改枢轴月过滤器动态

excel - VBA更改枢轴月过滤器动态

问题描述

我有以下代码来更改数据透视过滤器，我想从当前选择上一个月份，但代码没有做任何事情例如过滤器是“Sep”，我想转到“Oct”我们有 11 月我需要运行宏并将过滤器更改为“Oct”</p> 

Sub PivotChange()
Application.ScreenUpdating = False

Dim WBM As Workbook
Set WBM = Workbooks("MASTER SellOut.xlsx")
Dim SellOut As Worksheet
Set SellOut = WBM.Worksheets("SellOut")
Dim pf As PivotField

Set pf = SellOut.PivotTables("PivotTable2").PivotFields("Date")

Dim pi As PivotField
Dim strMonth As String

' current month as string
strMonth = Format(Date, "mmm")

' refresh pivottable

SellOut.PivotTables("PivotTable2").PivotCache.Refresh

With SellOut.PivotTables("PivotTable2").PivotFields("Date")
    On Error Resume Next
    ' check, if pivotfield exists
    Set pi = .PivotField(strMonth)
    On Error GoTo 0
    If Not pi Is Nothing Then
        pi.Visible = True
        For Each pi In .PivotField
            If pi.Name <> strMonth Then pi.Visible = False
        Next pi
    End If
End With

 Application.ScreenUpdating = True

 End Sub

标签： excelvba

解决方案

你不pi应该：PivotItemPivotField

Dim pi As PivotItem
...
With pf
    On Error Resume Next
    ' check, if pivotfield exists
    Set pi = .PivotItems(strMonth)
    On Error GoTo 0
    If Not pi Is Nothing Then
        pi.Visible = True
        For Each pi In .PivotItems
            If pi.Name <> strMonth Then pi.Visible = False
        Next pi
    End If
End With

推荐阅读