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

fmluder93

New Member
Joined
Nov 2, 2018
Messages
4
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top