Automate a change in data filters

Hi everyone! I have a pivot table to update every day by selecting in the filter the day by hand (and unchecking the previous day). I would like to automate the filter update so that a filter autom...

BI & Data Analysis

excel

Formulas and Functions

n0ahc

Copper Contributor

Oct 23, 2023

Rodrigo_ Thank you for your reply! I wasn't specific enough in my first message and I apologise for that. I need to compare 4 days. This means that each day, I need the filter to tick today, and untick 3 days ago, while keeping the middle two days.

Here's an example:
I open the sheet on Monday, I need to have the filters Thursday - Friday - Saturday - Sunday ticked.
I open the sheet on Tuesday, and I need to have the Friday - Saturday - Sunday - Monday filters ticked.
And so on.

Thanks again for your invaluable help!

Rodrigo_

Iron Contributor

Dec 15, 2023

n0ahc
try this one:

Private Sub Workbook_Open()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim dateColumn As Range

    ' Set the current date to today
    currentDate = Date

    ' Set the start date to 3 days ago
    startDate = currentDate - 3

    ' Set the end date to today
    endDate = currentDate

    ' Define the range of the date column in your data
    ' Change "A1:A500" to match the location of your date column
    Set dateColumn = ThisWorkbook.Worksheets("Sheet1").Range("A1:A500")

    ' Clear any existing filters
    dateColumn.AutoFilter

    ' Filter by the last four days
    dateColumn.AutoFilter Field:=1, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate
End Sub

Just replace the "Sheet1" and "A1:A500" with your sheet name and date column range. This code will execute when you open the workbook and automatically filtering the data based on the last 4 days.

Ps. Apologize for 2 months delayed reply, really been busy.

Forum Discussion

Automate a change in data filters

Resources