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





jlclyde wrote:

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

--

Dave Peterson

On Sep 16, 4:58*pm, Dave Peterson wrote:
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

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