Filter dynamic in pivot table

JValencia

New Member
Joined
Jun 21, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,720
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.

VBA Code:
    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...

VBA Code:
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!
 

Forum statistics

Threads
1,141,068
Messages
5,704,092
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top