Sort Sheets by Name

Here's an interesting one.. How does one sort sheets by the name of the sheet. For example, the company I work with have about 300 salesperson, and their performance are monitored on a single sheet. You can imagine the annoyance of having to look for the sheet name for 300 people. Thanks in advance guys!

[Dave Hawley]

Hi


Here's some code:

Sub SortSheets()
Dim i As Integer, ii As Integer
Dim ShtName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets.Add().Name = "SortingWorsheet"
On Error GoTo 0
If ActiveSheet.Name <> "SortingWorsheet" Then ActiveSheet.Delete

With ThisWorkbook.Sheets("SortingWorsheet")
.Columns(20).Clear

For i = 1 To ActiveWorkbook.Sheets.Count
.Cells(i, 20) = Sheets(i).Name
Next

.Columns(20).Sort Key1:=.Cells(1, 20), Order1:=xlAscending

For ii = i - 1 To 1 Step -1
ShtName = .Cells(ii, 20)
ActiveWorkbook.Sheets(ShtName).Move Before:=ActiveWorkbook.Sheets(1)
Next

End With
Sheets("SortingWorsheet").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Hey... Thanks Dave.. Love your site, am frequent visitor

[Dave Hawley]

Thanks! I was thinking of putting this code on there for all to use.