Sort all worksheets in a workbook

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

jlclyde

I am trying to sort all work sheets in a workbook by two different
keys. The keys are different for each case of worksheet. I am trying
to put together a For each and a Select case. Any help would be
appreciated. Code is below.

Thanks,
Jay

Sub EditSheets()
Dim Wks As Worksheet
Dim LstRow As Long
Dim LstCol As Long

For Each Wks In ActiveWorkbook.Worksheets
Select Case LCase(Wks.Name)
Case "hkips", "napkin", "nsf", "gietz", "offset", "drill",
"laser"
With Worksheets(Wks.Name)
LstCol = .Range("IV4").End(xlLeft).Column
LstRow = .Range("A65536").End(xlUp).Row

.Range(.Cells(5, 1), .Cells(LstRow, LstCol)).Sort _
key1:=.Range("E5"), Order1:=xlAscending, _
Key2:=.Range("A5"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
Case "rosback"
With Worksheets(Wks.Name)
LstCol = .Range("IV4").End(xlLeft).Column
LstRow = .Range("A65536").End(xlUp).Row

.Range(.Cells(5, 1), .Cells(LstRow, LstCol)).Sort _
key1:=.Range("D5"), Order1:=xlAscending, _
Key2:=.Range("A5"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
Case Else
With Worksheets(Wks.Name)
LstCol = .Range("IV4").End(xlLeft).Column
LstRow = .Range("A65536").End(xlUp).Row

.Range(.Cells(5, 1), .Cells(LstRow, LstCol)).Sort _
key1:=.Range("C5"), Order1:=xlAscending, _
Key2:=.Range("A5"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
End Select
Next Wks
End Sub
 
Jay,

What exactly do you need help with.

Regards,
Per

"jlclyde" <[email protected]> skrev i meddelelsen







- Show quoted text -
Click to expand...

I was looking for a way to sort all worksheets by two seperate keys.
I figured that part out and now need to resort the sheets by a
different key. I woudl like to Sort all sheets in the work book by A5
ascending. How is this done?
Thanks,
Jay
 
First, just a couple of points...

You'll want to use xltoleft, not xlleft. There's a difference:
?xltoleft
-4159
?xlleft
-4131
(from the VBE's immediate window)

I would use
with wks
instead of
with worksheets(wks.name)

And instead of using A65536 and IV4, I'd let excel determine the last row and
last column in the sheet:

Option Explicit
Sub EditSheets2()
Dim Wks As Worksheet
Dim LstRow As Long
Dim LstCol As Long

For Each Wks In ActiveWorkbook.Worksheets
With Wks
LstCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range(.Cells(5, 1), .Cells(LstRow, LstCol)).Sort _
key1:=.Range("a5"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Next Wks
End Sub




jlclyde wrote:
 
Back
Top