ExcelBanter - Sort all worksheets in a workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sort all worksheets in a workbook (https://www.excelbanter.com/excel-discussion-misc-queries/211700-sort-all-worksheets-workbook.html)

Sort all worksheets in a workbook

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

Sort all worksheets in a workbook

Jay,

What exactly do you need help with.

Regards,
Per

"jlclyde" skrev i meddelelsen
...
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

Sort all worksheets in a workbook

On Nov 26, 8:30*am, "Per Jessen" wrote:
Jay,

What exactly do you need help with.

Regards,
Per

"jlclyde" skrev i ...

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- Hide quoted text -

- Show quoted text -

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

Sort all worksheets in a workbook

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:
<<snipped
- Show quoted text -

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

--

Dave Peterson