Macro to filter data based on number of days action is due from today's date
Results 1 to 4 of 4

Thread: Macro to filter data based on number of days action is due from today's date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to filter data based on number of days action is due from today's date

    Hi there,
    I have created an action plan that has a main menu that filters the action plan as required. One of the macros looks at filtering for items that are due within either 15 days, 30 days or now (I don’t have one for overdue yet). I have set up based on some advice what the macro should be, but am having issues in the data filtering. Its does, but doesn’t stay visible in the cell.

    I have looked for a solution, but am stumped!

    This is the I am using:

    Sub FifteenDays()
    Sheets("HSE Master Action Plan").Select
    ActiveSheet.Range("$b$4:$s$600").AutoFilter Field:=10, Criteria1:=Array("15", "14", "13", "12", "11", "10", "9", "8", "7", "6", "5", "4", "3", "2", "1")
    End Sub

    I am having issues attaching the spreadsheet as looks like i dont have permissions. But the above is a macro i am using. Its similar to 30 days as well but counts down from 30 to 16 as noted above.

    Cheers …. Amanda

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to filter data based on number of days action is due from today's date

    Try these macros for all cases.

    Code:
    Public Sub Due_Within_15_Days()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:=">=" & CDbl(Date), Operator:=xlAnd, Criteria2:="<=" & CDbl(Date + 15)
    End Sub
    
    Public Sub Due_Within_30_Days()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:=">=" & CDbl(Date), Operator:=xlAnd, Criteria2:="<=" & CDbl(Date + 30)
    End Sub
    
    Public Sub Due_Today()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:=">=" & CDbl(Date), Operator:=xlAnd, Criteria2:="<=" & CDbl(Date)
    End Sub
    
    Public Sub Overdue()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:="<" & CDbl(Date)
    End Sub
    Assumes column K (field 10) is a date.
    Last edited by John_w; Feb 11th, 2019 at 07:38 AM.

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to filter data based on number of days action is due from today's date

    Hi there,

    Thank you so much for your reply. Field 10 is actually the number of days until the action is due (refer table below) and is not actually a date. The number of days are calculated from current days date and end date. The intent is for our action owners to be able to filter out what is coming up. So the macro coding is designed to just display items overdue, today, and 15 or 30 days.

    (field 10)
    (16 days until action due) - Start Date 24/01/19 End Date 28/02/19 (16 days being days until item is due from today to end date)
    (31 days until action due) - Start Date 24/01/19 End Date 15/03/19 (31 days being days until item is due from today to end date)

    I hope the above gives more clarity.

    Cheers .... Amanda

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to filter data based on number of days action is due from today's date

    In that case:
    Code:
    Public Sub Due_Within_15_Days()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:=">=1", Operator:=xlAnd, Criteria2:="<=15"
    End Sub
    
    Public Sub Due_Within_30_Days()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:=">=1", Operator:=xlAnd, Criteria2:="<=30"
    End Sub
    
    Public Sub Due_Today()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:="=0"
    End Sub
    
    Public Sub Overdue()
        Sheets("HSE Master Action Plan").Range("B4").CurrentRegion.AutoFilter Field:=10, Criteria1:="<0"
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •