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

[fmluder93]

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

 

[John_w]

Try these macros for all cases.

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.

 

[fmluder93]

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

 

[John_w]

In that case:

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