Forum Discussion

n0ahc's avatar
n0ahc
Copper Contributor
Oct 12, 2023

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 automatically selects the right day each day.

Can you help me?
Thanks in advance!
Noah

BI & Data Analysis
excel
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Oct 14, 2023

    n0ahc 

    Hello,
    it is possible for VBA to do that. You can automate the filter to run when you open the workbook. To do this, you can use Excel's "Workbook Open" event in VBA.

     

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

    ' Set the current date to today
    currentDate = Date

    ' 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 current date
    dateColumn.AutoFilter Field:=1, Criteria1:=currentDate
End Sub

     

     

    Replace "Sheet1" and "A1:A500" with your actual sheet name and date column range.

    Once you've made these replacements, save your workbook, and the code will execute when you open it, automatically filtering the data based on the current date.

    • n0ahc's avatar
      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_'s avatar
        Rodrigo_
        Steel 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.

Resources