Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Worksheets (without VB) | Excel Worksheet Functions | |||
Is there a way to sort worksheets within a workbook | Excel Discussion (Misc queries) | |||
How do I sort data from various worksheets in the same workbook i. | Excel Worksheet Functions | |||
Sort Worksheets | Excel Worksheet Functions | |||
I want to sort worksheets in a workbook into Alpha order? | Excel Worksheet Functions |