Sort multiple columns on all worksheets

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

I am trying to sort each worksheet by 2 different columns. I want all
sheets to be sorted by column A ascending and then some sheets sorted
by E and the rest by C. Here is the code that I Have. It does nto do
what I want, but I feel that it is close. any help would be greatly
appreciated. Here is the code.

Thanks,
Jay

Sub SortSheets()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
Dim Ws As String
Ws = wsSheet.Name
If Ws <> "Rosback" And Ws <> "Cutting" And Ws <> "Gluer" And Ws <>
"Folding" And _
Ws <> "Lookup Sheet" Then

Range("A5:AZ5000").Sort Key1:=Range("A4"),
Order1:=xlAscending, _
key2:=Range("E4"), Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Ws = "Rosback" Or Ws = "Cutting" Or Ws = "Folding" Or Ws =
"Gluer" Then
Range("A5:AZ5000").Sort Key1:=Range("A4"),
Order1:=xlAscending, _
key2:=Range("c4"), Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
End If
Next wsSheet
End Sub
 
Option Explicit
Sub SortSheets()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
With wsSheet
Select Case LCase(.Name)
'all lower case!!!!
Case Is = "rosback", "cutting", "gluer", _
"folding", "lookup sheet"
.Range("A5:AZ5000").Sort _
Key1:=.Columns(1), Order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Case Else
.Range("A5:AZ5000").Sort _
Key1:=.Columns(1), Order1:=xlAscending, _
key2:=.Columns(5), order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Select
End With
Next wsSheet
End Sub

notice the dots (.) in front of those .range()'s and .columns(). That means
that they belong to the object in the previous with statement.

And since you're really sorting the same stuff--just by a different secondary
key, you could do something like:

Option Explicit
Sub SortSheets()
Dim wsSheet As Worksheet
Dim my2ndKey As Long

For Each wsSheet In Worksheets
With wsSheet
Select Case LCase(.Name)
'all lower case!!!!
Case Is = "rosback", "cutting", "gluer", _
"folding", "lookup sheet"
my2ndKey = 3
Case Else
my2ndKey = 5
End Select
.Range("A5:AZ5000").Sort _
Key1:=.Columns(1), Order1:=xlAscending, _
key2:=.Columns(my2ndKey), order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next wsSheet
End Sub
 
Option Explicit
Sub SortSheets()
    Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
        With wsSheet
            Select Case LCase(.Name)
                'all lower case!!!!
                Case Is = "rosback", "cutting", "gluer", _
                          "folding", "lookup sheet"
                    .Range("A5:AZ5000").Sort _
                        Key1:=.Columns(1), Order1:=xlAscending, _
                        key2:=.Columns(3), order2:=xlAscending, _
                        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                        Orientation:=xlTopToBottom
                Case Else
                    .Range("A5:AZ5000").Sort _
                        Key1:=.Columns(1), Order1:=xlAscending, _
                        key2:=.Columns(5), order2:=xlAscending, _
                        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                        Orientation:=xlTopToBottom
            End Select
        End With
    Next wsSheet
End Sub

notice the dots (.) in front of those .range()'s and .columns().  That means
that they belong to the object in the previous with statement.

And since you're really sorting the same stuff--just by a different secondary
key, you could do something like:

Option Explicit
Sub SortSheets()
    Dim wsSheet As Worksheet
    Dim my2ndKey As Long

    For Each wsSheet In Worksheets
        With wsSheet
            Select Case LCase(.Name)
                'all lower case!!!!
                Case Is = "rosback", "cutting", "gluer", _
                          "folding", "lookup sheet"
                    my2ndKey = 3
                Case Else
                    my2ndKey = 5
            End Select
            .Range("A5:AZ5000").Sort _
                Key1:=.Columns(1), Order1:=xlAscending, _
                key2:=.Columns(my2ndKey), order2:=xlAscending, _
                Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom
        End With
    Next wsSheet
End Sub
Click to expand...
Dave,
I tried both of these to no avail. What I did where it finally worked
was to do each part seperate. I sorted by the A then I had another
for each statement and did the final sorting. After thinking about
this, I divided it into 2 subs anyways. When it is all said and done
I will ahve to sort by column A to put the dates in order. Your code
definitly made me see the light.
Thanks for your help,
Jay
 
Back
Top