Multiple Criterion Sort on Multiple Sheets With One Code

[reberryjr]

Ok, I recorded a macro to sort a spreadsheet the way I need. That's the first code posted below. The issue is, I have 5 sheets that I want sorted the same way. I took a stab at coding for this, leveraging some other code that I have (2nd code set), but I'm getting an "Unable to Find Sort Property" error. I've googled and searched in this forum, but I'm not finding anything that shows how to abbreviate the code, to account for 5 worksheets.

    Range("A1").Select    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("DC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DC").Sort.SortFields.Add Key:=Range("D2:D3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("DC").Sort.SortFields.Add Key:=Range("B2:B3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DC").Sort
        .SetRange Range("A1:T3")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Private Sub Workbook_Open()

Dim ws As Worksheet


For Each ws In Worksheets
    If Not ws.Name = "Bios" Then
        ws.Sort.SortFields.Clear
        ws.Columns("A:T").Sort.SortFields.Add key1:=ws.Columns("D"), SortOn1:=xlSortOnValues, Order1:=xlAscending
        ws.Columns("A:T").Sort.SortFields.Add key2:=ws.Columns("B"), SortOn2:=xlSortOnValues, Order2:=xlDescending
    End If
Next ws
End Sub

 

[reberryjr]

I found an additional post that gave me the first key line, which worked. However, when I add the second key, nothing sorts at all.

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim LastRow As Long


For Each ws In Worksheets
    If Not ws.Name = "Bios" And Not ws.Name = "Stats" Then
    LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
        ws.Range("A1:T" & LastRow).Sort Key1:=ws.Range("D1:D" & LastRow), Order1:=xlAscending, Header:=xlYes
        ws.Range("A1:T" & LastRow).Sort Key1:=ws.Range("B1:B" & LastRow), Order1:=xlAscending, Header:=xlYes
    End If
    
Next ws


End Sub

 

[reberryjr]

Officially at a loss. I tried this and I get the Sort Method of Range Class error on the red line.

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim LastRow As Long


For Each ws In Worksheets
    If Not ws.Name = "Bios" And Not ws.Name = "Stats" Then
    LastRow = ws.Range("E" & Rows.Count).End(xlUp).Row
        With ws.Range("E" & LastRow)
        ws.Range("A1:T" & LastRow).Sort Key1:=ws.Range("E1:E" & LastRow), Order1:=xlAscending, Header:=xlYes
[COLOR=#ff0000]        ws.Range("A1:T" & LastRow).Sort Key2:=ws.Range("B1:B" & LastRow), Order2:=xlAscending, Header:=xlYes[/COLOR]
        End With
    End If
    
Next ws


End Sub

 

[dmt32]

Hi,
not fully tested but see if following does what you want:


Place in STANDARD module:

Sub SortSheets(ByVal sh As Object, ParamArray SortFields() As Variant)
    Dim Field As Variant
    With sh.Sort
        .SortFields.Clear
        
        For Each Field In SortFields
            .SortFields.Add Key:=sh.Range(Field), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Next
        
        .SetRange sh.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Place in Thisworkbook Code Page

Private Sub Workbook_Open()


    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If Not ws.Name = "Bios" And Not ws.Name = "Stats" Then SortSheets ws, "D1", "B1"
    Next ws


End Sub

The ParamArray argument should allow you to specify as many Sort Fields as required.

As always, make backup of your workbook before testing new code.

Dave

 

[reberryjr]

Thanks for the code. I'll try it, but I'm curious if you can identify what the issue with my code is. Trying to understand where I went wrong, for future reference.

 

[reberryjr]

@dmt32 I pasted the code you provided and ran it. I get an error that says, "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank." When I debug, it highlights this line.

.Apply

I went back and checked the 5 sheets it should be hitting, and all of them have data in the two columns being sorted on, so I'm not sure what would be causing this error.

 

[dmt32]

Interesting, made a dummy workbook & code ran fine.

Assuming you have made no changes to published code - Try this change

     .Apply
    End With


    sh.Sort.Apply

delete line shown in red & add line below & see if this resolves your issue

Dave

 

[reberryjr]

So, I'm not very smart. LOL! I went back and looked at the data again. It actually was working. I thought I had a date of 1/5/18, and it was really 1/5/17, so I was thinking it should have been at the bottom of the sorted column. I also resolved the error by correcting an oversight. I forgot to exclude an additional sheet.

Thanks for your assistance!

 

[dmt32]

glad resolved

Dave