ID: 9
Category: Excel Automation
Question: How do I alphabetically sort the sheets in the workbook?
Answer: Put the following in a standard module of a workbook. Use it to sort the active workbook Option Explicit
Sub sortSheets()
'Sorts the sheets in the activeworkbook by sheet name. Uses a new _
XL workbook to actually sort the name. This allows for a single _
pass algorithm, such that each sheet is moved into the correct _
position the one and only time it is moved. _
For alternative algorithms see _
XL: How to Sort Sheets in a Workbook _
http://support.microsoft.com/default.aspx?scid=kb;en-us;812386 _
and _
Sorting Worksheets In A Workbook _
http://www.cpearson.com/excel/sortws.htm
Const sortAscending As Boolean = True
Dim srcWB As Workbook, tempWB As Workbook, aWKS As Worksheet, _
i As Long
Set srcWB = ActiveWorkbook
If srcWB.Sheets.Count = 1 Then Exit Sub '<<<<
Set tempWB = Workbooks.Add
Set aWKS = tempWB.Worksheets.Add
aWKS.UsedRange.ClearContents
With aWKS.Range("a1")
For i = 1 To srcWB.Sheets.Count
.Offset(i - 1, 0).Value = srcWB.Sheets(i).Name
Next i
End With
aWKS.Range("A1").End(xlDown).Sort Key1:=Range("A1"), _
Order1:=IIf(sortAscending, xlAscending, xlDescending), _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With srcWB
For i = srcWB.Sheets.Count To 1 Step -1
.Sheets(aWKS.Range("a1").Offset(i - 1, 0).Value).Move _
before:=.Sheets(1)
Next i
End With
tempWB.Close False
End Sub
ExtendedLink:
|