Sort EXCEL sheets into alphabetical order

[KymY]

I have a workbook with 50 sheets (1 for each employee).

How can I get Excel to sort these into alphabetical order ?

(From [email protected])

 

[Rowan]

This macro should do it.

Sub SortSheets()

Dim sht As Worksheet
Dim mySht As Worksheet
Dim i As Integer
Dim endRow As Long
Dim shtNames As Range
Dim Cell As Range

Set mySht = Sheets.Add
mySht.Move before:=Sheets(1)

For i = 2 To Sheets.Count
mySht.Cells(i - 1, 1).Value = Sheets(i).Name
Next i

endRow = mySht.Cells(Rows.Count, 1).End(xlUp).row
Set shtNames = mySht.Range(Cells(1, 1), Cells(endRow, 1))

shtNames.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1

i = 2
For Each Cell In shtNames
Sheets(Cell.Value).Move before:=Sheets(i)
i = i + 1
Next Cell

Application.DisplayAlerts = False
mySht.Delete
Application.DisplayAlerts = True

End Sub


Regards
Rowan

 

[KymY]

I have a workbook with 50 sheets (1 for each employee).

How can I get Excel to sort these into alphabetical order ?

(From [email protected])
Thanks, mate - have pasted the script into my Lotus Organizer Tips notepad

 

[David McRitchie]

Sorting worksheet tabs into alphabetical order:

A list of worksheets in a worksheet may be useful, but it is
not necessary in order to sort the worksheets, and since that
worksheet is deleted anyway wouldn't seem practical.

see SortAllSheets macro in
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm .