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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,305
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:

fmluder93

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,305
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,848
Messages
5,483,289
Members
407,391
Latest member
sumantskj

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top