Automatic Excel row sorting based on multiple criteria

I am at a loss trying to automatically sort an Excel spreadsheet. I have included an example of the data. Basically, I need the sheet to automatically sort the rows based on the order number and whether or not the last 4 columns contain "Y". If H-K all contain Y, then they need to be brought to the top and sorted by order number. Then, if only H-J contain Y, they should be below and also sorted by order number and so on. Rows that do not contain Y in any of the cells H-K should be at the bottom and still sorted by order number. Its my understanding that I will need to use VBA, but I am new to coding and I'm desperately trying to get this worked out for my coworker who is in his 70's. To my knowledge, the spreadsheet did this sorting for years, but I think something happened and now it is not.

If anyone can help me with this I would really appreciate it!

I have tried looking at videos, questions here in stackoverflow, and code examples provided by excel, but to no avail.

Option Explicit

Sub mysort()

    Dim ws As Worksheet, rng As Range, lastrow As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
       
        ' add helper column L
        Set rng = .Range("L2:L" & lastrow)
        rng.FormulaR1C1 = "=COUNTIF(RC[-4]:RC[-1],""Y"")"
       
        ' sort
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=rng _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add2 Key:=Range("B2:B" & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange ws.Range("A1:L" & lastrow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
      
    ' clear helper
    rng.Clear

End Sub