How can I modify this macro so it sorts by months

Question

I have a macro that sorts data into separate spreadsheets based on a 2nd tabs list. I'd like to know how I can modify it so that it can split the data based on how far away it is from Todays date, 30 days, 60 days, 90 Days.

Attached are 2 images of my current sheets. The goal is to have it sort into separate sheets the data based on how far away the due date is vs todays date, the following code I use was able to do that for the Students grades, but anytime I try to do it for the date it doesn't recognize the numbers. I used a cell formula to give me a value for how many months away the date is vs todays date.

Sub SplitandFilterSheet()

Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=5, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub

Answer 1

I don't know macros honestly, but I asked your question to OpenAI and it gave me this answer:

https://beta.openai.com/playground

create an excel macro code that will sort by month

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheet("Sheet1").Sort.SortFields.Add Key:=Range("B1:B10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheet("Sheet1").Sort
.SetRange Range("A1:C100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

Answer 2

Hi Victor,

Looks like you are not too far away on this.

You could try replacing the grade in the splitCode range with the dates that you want to split by, change the filter field to the date and then filter the results that are less than the cell value.

Dates can be tricky, I find it is usually best to make sure all dates are formatted as "Short Date" and make sure that the correct date is showing when formatted.

You might need to create another column in the splitCode range for the sheet names if the date doesn't work as a sheet name.

Useful functions when dealing with dates include =DATEVALUE and =TEXT(A1,"mmmyy")

Hope this helps!