![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Guest
Posts: n/a
|
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!
|
|
|
|
#2 |
|
Banned
Join Date: Feb 2002
Posts: 1,582
|
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 |
|
|
|
|
|
#3 |
|
Guest
Posts: n/a
|
Hey... Thanks Dave.. Love your site, am frequent visitor
|
|
|
|
#4 |
|
Banned
Join Date: Feb 2002
Posts: 1,582
|
Thanks! I was thinking of putting this code on there for all to use.
|
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|