Multiple Criterion Sort on Multiple Sheets With One Code

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
    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

Code:
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows
Officially at a loss. I tried this and I get the Sort Method of Range Class error on the red line.

Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,392
Office Version
  1. 2019
Platform
  1. Windows
Hi,
not fully tested but see if following does what you want:


Place in STANDARD module:

Code:
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

Code:
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
 
Last edited:

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows
@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.
Code:
.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

Well-known Member
Joined
Jul 3, 2012
Messages
6,392
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Interesting, made a dummy workbook & code ran fine.

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

Rich (BB code):
     .Apply
    End With


    sh.Sort.Apply

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

Dave
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
501
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,392
Office Version
  1. 2019
Platform
  1. Windows
glad resolved

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top