Help with my code

[monkeyharris]

Hi all,
I recorded a Macro to make the same task easier. problem is, there's a section to automatically filter then select a date. Obviously the macro will then always filter by the date i selected. Is there a way to make this always filter by the next day. IE, if today is 17/10/23, can the code automatically look for 18/10/23?
Thinking ahead for my next problem which will be on a Friday, it would need to look up the Monday as we don't work weekends. If that's too much to ask and someone can help with the first problem, that's great. Code below:

Sub OpenSalesOrders()
'
' OpenSalesOrders Macro
'

'
    Columns("J:L").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$U$335").AutoFilter Field:=13, Criteria1:= _
        "Delivery"
    Columns("A:B").Select
    Range("B1").Activate
    Selection.Delete Shift:=xlToLeft
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("B1:B335"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("B1:B335"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$S$335").AutoFilter Field:=2, Criteria1:= _
        "18/10/2023"
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("M1:M335"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4").Select
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[monkeyharris]

Apologies Peter, that certainly looks better. Now i just need a solution

 

[Michael M]

Maybe

ActiveSheet.Range("$A$1:$S$335").AutoFilter Field:=2, Criteria1:=Date+1

 

[monkeyharris]

Maybe

ActiveSheet.Range("$A$1:$S$335").AutoFilter Field:=2, Criteria1:=Date+1

Hi Michael,

I changed it so that line now says ActiveSheet.Range("$A$1:$S$335").AutoFilter Field:=2, Criteria1:=Date + 1, but it removed the date completely and hid the remaining dates. Basically had a plan page apart from the headers.

 

[kevin9999]

Another option (assumes dates are in column B)

ActiveSheet.Range("$A$1:$S$335").AutoFilter 2, 3, 11

 

[monkeyharris]

Another option (assumes dates are in column B)

ActiveSheet.Range("$A$1:$S$335").AutoFilter 2, 3, 11

Hi Kevin, It's probably something i've done wrong but everything seems to disappear. I've put the code back to how i originally had it and it filters column B to 18th. Can i confirm the code i need to replace from my original table is the below and replace with your new code?

ActiveSheet.Range("$A$1:$S$335").AutoFilter Field:=2, Criteria1:= _
        "18/10/2023"

 

[kevin9999]

That tends to suggest that your 'dates' are actually text that look like dates. (do they left-align in the cell?) Add the following and see if it makes a difference:

Dim mydate As String
mydate = Format(Date + 1, "dd/mm/yyyy")
ActiveSheet.Range("$A$1:$S$335").AutoFilter 2, mydate

 

[monkeyharris]

That tends to suggest that your 'dates' are actually text that look like dates. (do they left-align in the cell?) Add the following and see if it makes a difference:

Dim mydate As String
mydate = Format(Date + 1, "dd/mm/yyyy")
ActiveSheet.Range("$A$1:$S$335").AutoFilter 2, mydate

You are fantastic, worked a treat.

 

[kevin9999]

Happy to help & thanks for the feedback