Create a macro button to show due dates "this week" or "this month"

[julesfurman]

Hello,

I am trying to create a macro button that shows due dates for "this week" and "the next two weeks" and "next month". I have delivery due dates in column K in a spreadsheet and at the click of a button, I would like to display only the rows that have due dates for this week, etc.

Also, I am not creating the macros in our live spreadsheet; I am using a copy of the spreadsheet in case I make an error. Once I have created the macro buttons that work, how do I get them into our main spreadsheet?

Thank you in advance for any help you can provide.
Jules

 

[gruntingmonkey]

Can you not use the conditional formatting which has a dates this week/month function? - I use Excel 2013 not sure if this available on any other versions

 

[MaxSmart]

I have had a bit of a play with this. My solution would be four macros
One for each button (this week, next fortnight, next month)
Here they are;

Sub Macro1()
' This week
Dim FilType As Integer
FilType = 1
Call GetRange(FilType)
End Sub

Sub Macro2()
' Next Fortnight
Dim FilType As Integer
FilType = 2
Call GetRange(FilType)

End Sub

Sub Macro3()
' Next month
Dim FilType As Integer
FilType = 3
Call GetRange(FilType)

End Sub
Sub GetRange(FilType)
'
Dim CurDate As Date
Dim CurWkDay As Integer
Dim WkStart As Date
Dim WkEnd As Date
Dim RangeA As Variant
Dim RangeB As Variant
'
Dim FilStart As Date
Dim FilEnd As Date
'
CurDate = Date
CurWkDay = ((CurDate - 2) Mod 7) + 1 ' Day 1 is Monday
CurWkStart = (CurDate - CurWkDay) + 1
CurWkEnd = CurWkStart + 6 ' Week ends on Sunday
'
Select Case FilType
Case 1 ' This week
FilStart = CurWkStart
FilEnd = CurWkEnd
Case 2 ' Next fortnight
FilStart = CurWkEnd + 1
FilEnd = CurWkEnd + 15
Case 3 ' Next Month
FilStart = DateSerial(Year(CurDate), Month(CurDate) + 1, 1)

FilEnd = DateSerial(Year(FilStart), Month(FilStart) + 1, 0)
End Select
'
RangeA = ">=" & Format(FilStart, "DDMMMYY")
RangeB = "<=" & Format(FilEnd, "DDMMMYY")
'
ActiveSheet.Range("K:K").AutoFilter Field:=1, Criteria1:= _
RangeA, Operator:=xlAnd, Criteria2:=RangeB

End Sub

Hope this helps