Improve VBA which filters pivot table field with current month months to the year end-CodePudding

I have a VBA macro that filters the pivot table as follows: it takes current month remaining months until the end of the year including the last month of the year (December). Currently everything is working. However, I got an error when I put December as current month: it won't unfilter previous month - November, so I made an "errhandler" which removes filter from November.

I believe there is a way to improve this and make code simplier. However I cannot find the way how to do this. If you have any suggestions/ideas how to make this code simpler, I would really appreciate it!

The code is below:

Sub FilterMonth()
Dim mthname, m As Integer
    mNum = 12 'Format(Date, "m")
    mName = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
                        
    If mNum = 12 Then
        On Error GoTo errhandler
        For i = mNum To LBound(mName) Step -1
            mPast = mName(i   1)
            ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period") _
            .PivotItems(mPast).Visible = False
        Next i
        
errhandler:
    ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period") _
    .PivotItems("Nov").Visible = False
    Resume Next
    
    Else
    
        For i = mNum To LBound(mName)   1 Step -1
            mPast = mName(i)
            ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period") _
            .PivotItems(mPast).Visible = False
        Next i
    
        For n = mNum To UBound(mName)
            mNow = mName(n)
            ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period") _
            .PivotItems(mNow).Visible = True
        Next n
        
    End If
    
End Sub

CodePudding user response：

Try something like the code below, fins explanation inside the code's comments:

Option Explicit


Sub FilterMonth()


Dim mthname As String
Dim mName As Variant
Dim i As Long, MatchRow As Variant, MonthsClearArr() As String


Application.ScreenUpdating = False

'    mNum = 12 'Format(Date, "m")
    mthname = "Dec"  ' <-- for testing purposes
    mName = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
                        
    ' remove all filters from Pivot field
    ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period").ClearAllFilters
          
    ' Use Match to get the Month Numner inside the array
    MatchRow = Application.Match(mthname, mName, 0)
    
    If Not IsError(MatchRow) Then
        ' save all month names to save in an array
        ReDim MonthsClearArr(1 To MatchRow - 1)
        For i = 1 To MatchRow - 1
            MonthsClearArr(i) = mName(i - 1)
        Next i
        
        ' loop over ne array of months to hide, and per month hide
        For i = 1 To UBound(MonthsClearArr)
            On Error Resume Next
            ThisWorkbook.Sheets("SCC").PivotTables("SCC").PivotFields("Period") _
            .PivotItems(MonthsClearArr(i)).Visible = False
            On Error GoTo 0
        Next i
   
    Else ' error finding Month Name
        ' display error message
    
    End If

Application.ScreenUpdating = True

                 
End Sub

Also, would be better to set all of your objects, including Pivot-Table and Pivot-Field:

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField

' set the Pivot-Table object
Set PvtTbl = ThisWorkbook.Sheets("SCC").PivotTables("SCC")
Set PvtFld = PvtTbl.PivotFields("Period")

CodePudding user response：

I assume you want to hide past months and show current plus future

Sub FilterMonth()

    Dim mthname, m As Integer, ws As Worksheet
    mNum = 6 'Format(Date, "m")
    mName = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
                        
    Set ws = ThisWorkbook.Sheets("SCC")
    With ws.PivotTables("SCC").PivotFields("Period")
        For i = 1 To 12
            .PivotItems(mName(i)).Visible = CBool(i >= mNum)
        Next
    End With
    
End Sub