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])
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])
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" wrote:
> 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])
>
"KymY" wrote:
> 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
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 .
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks