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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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