Sort Sheets by Name

G

Guest

Guest
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top