Filter dynamic in pivot table

[JValencia]

Hello everyone, colleagues I need your great help with this case, I have been doing an automation in UiPath (RPA) of a process where a management report is generated, everything is done in excel and I have managed to do it through excel activities and most of all by invoking VBA, but I have reached a knot and I don’t know how to solve it.
In a part of the report, a pivot table is generated, which I have managed to update and filter (the current month is filtered and the same month but last year, that is, 2021-06 and 2020-06) that I did through this code

Function SelectFilter (currentMonth, previousMonth)

Worksheets ("Total Company"). PivotTables ("PivotTable1"). PivotFields ("Calendar Year / Month"). _
CurrentPage = "(All)"
With Worksheets ("Total Company"). PivotTables ("PivotTable1"). PivotFields ("Calendar Year / Month")
.PivotItems (currentMonth) .Visible = True
.PivotItems (previousMonth) .Visible = True
End With

End Function

But now I am updating another pivot table but this shows what has accumulated throughout the year compared to what was carried last year, that is, it is filtered (2021-01,2021-02,2021-03,2021-04 , 2021-05,2021-6,2020-01,2020-02,2020-03,2020-04,2020-05,2020-06) obviously the amount of filtered data will change as the months progress, how can I do this?

(sorry for my english, I don't handle it very well)

 

[Domenic]

Hi, and welcome to the forum!

The following macro will automatically adjust the periods as each month progresses. So next month the periods would be 2021-01,2021-02,2021-03,2021-04 , 2021-05,2021-6,2021-07,2020-01,2020-02,2020-03,2020-04,2020-05,2020-06,2020-07, and so on. And, when a new year occurs, the periods will automatically adjust as well. So come January, 2022, the periods would be 2022-01,2021-01, and so on as each month progresses.

    Dim currentPivotItem As PivotItem
    Dim currentYearStart As Date
    Dim currentYearEnd As Date
    Dim lastYearStart As Date
    Dim lastYearEnd As Date
    Dim dt As Date
    Dim includeItem As Boolean

    With Worksheets("Total Company").PivotTables("PivotTable1").PivotFields("Calendar Year / Month")
        .ClearAllFilters
        currentYearStart = DateSerial(Year(Date), 1, 1)
        currentYearEnd = DateSerial(Year(Date), Month(Date), 1)
        lastYearStart = DateSerial(Year(Date) - 1, 1, 1)
        lastYearEnd = DateSerial(Year(Date) - 1, Month(Date), 1)
        For Each currentPivotItem In .PivotItems
            dt = CDate(currentPivotItem.Caption)
            If dt >= currentYearStart And dt <= currentYearEnd Then
                includeItem = True
            ElseIf dt >= lastYearStart And dt <= lastYearEnd Then
                includeItem = True
            Else
                includeItem = False
            End If
            If Not includeItem Then
                currentPivotItem.Visible = False
            End If
        Next currentPivotItem
    End With

By the way, since your function SelectFilter() is not returning anything, you could convert it into a Sub instead...

Sub FilterPivotTable()

    Dim currentPivotItem As PivotItem
    Dim currentYearStart As Date
    Dim currentYearEnd As Date
    Dim lastYearStart As Date
    Dim lastYearEnd As Date
    Dim dt As Date
    Dim includeItem As Boolean

    With Worksheets("Total Company").PivotTables("PivotTable1").PivotFields("Calendar Year / Month")
        .ClearAllFilters
        currentYearStart = DateSerial(Year(Date), 1, 1)
        currentYearEnd = DateSerial(Year(Date), Month(Date), 1)
        lastYearStart = DateSerial(Year(Date) - 1, 1, 1)
        lastYearEnd = DateSerial(Year(Date) - 1, Month(Date), 1)
        For Each currentPivotItem In .PivotItems
            dt = CDate(currentPivotItem.Caption)
            If dt >= currentYearStart And dt <= currentYearEnd Then
                includeItem = True
            ElseIf dt >= lastYearStart And dt <= lastYearEnd Then
                includeItem = True
            Else
                includeItem = False
            End If
            If Not includeItem Then
                currentPivotItem.Visible = False
            End If
        Next currentPivotItem
    End With
    
End Sub

Hope this helps!