VBA to sort 4 criteria in order

[vavs]

I have 6 columns in a spreadsheet. I need to sort the spreadsheet based on Column B then Column C then Column D and finally Column A. Columns B and C contain array formulas. Column D contains either Yes or No. I was planning on sorting this column in descending order. Finally Column A is both text and numbers. I would like to sort this as anything that looks like a number is a number.

Here is what I put in place. The only problem is that I am getting inconsistent results. I have approximately 10K rows of data.

Sub Cleanup()
    Application.StatusBar = "Filter and sort and you are done."
    Worksheets("USCopy1").Select
    Columns("A:F").Select
    
    
    
    ActiveWorkbook.Worksheets("USCopy1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("USCopy1").Sort.SortFields.Add Key:=Range( _
        "B:B"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("USCopy1").Sort.SortFields.Add Key:=Range( _
        "D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("USCopy1").Sort.SortFields.Add Key:=Range( _
        "C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("USCopy1").Sort.SortFields.Add Key:=Range( _
        "A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("USCopy1").Sort
        .SetRange Range("A:F")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("A:F").Select
    ActiveSheet.Range("A:F").AutoFilter
    
End Sub

So what I eventually need to do is Sort the column B so that the largest number is in row 1, then any duplicate values from B sort column C in status of Yes before No, any duplicate Yes or No, sort by the oldest date in Column D, finally alpha numeric sort of column A after all criteria is met.

 

[patel45]

attach a link to a sample file