Alphabetize Sheet Tabs?

[tabcourt]

I've got a bunch of worksheets that I'd like to re-arrange in alphabetical order. Anyway to do this quickly? Or do I just have to drag and drop them myself? (WXP, Excel XP). Thanks.

 

[larrydunn]

Here's my attempt to make the slight change needed to make the tabs before the Main sheet the only ones that get sorted.

As this is a highly specialized routine, I made the assumption
that the user would call it FROM the workbook being sorted -
and from the Main worksheet before which the sorted tabs will be
placed. That allows several simplifications.

I assumed also that any hidden sheets could be moved behind
the Main sheet. That leaves new sheets to the fore, where they
can be easily sorted and positioned in front of the Main sheet.

Once again: this routine must be called from the first sheet that is not to be sorted!

Sub SortSheets()

'this routine sorts the sheets of the active workbook in
'ascending order, putting new sorted sheets at front, hidden sheets at end

Dim SheetNames() As String
Dim I, J As Integer
Dim SheetCount As Integer
Dim OldActive As String
Dim Temp As String

'determine the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count

'store a reference to the active sheet
OldActive = ActiveSheet.Name

'turn off screen updating
Application.ScreenUpdating = False

'Move any hidden sheets after Main sheet
AnyHidden:
I = 0
NextHidden:
I = I + 1
If Sheets(I).Name = OldActive Then GoTo GetSheets 'Reached main sheet
If Sheets(I).Visible = True Then GoTo NextHidden
Sheets(I).Move After:=Sheets(OldActive)
GoTo AnyHidden

GetSheets:
ReDim SheetNames(SheetCount)
I = 0

FillArray:
I = I + 1
Sheets(I).Select
If Sheets(I).Name = OldActive Then GoTo ArrayFilled
SheetNames(I) = ActiveSheet.Name
GoTo FillArray

ArrayFilled:
SheetCount = I - 1 'Sort only visible sheets before Main

For I = 1 To (SheetCount) - 1
For J = (I + 1) To SheetCount
If SheetNames(I) > SheetNames(J) Then GoTo NoSort
Temp = SheetNames(I)
SheetNames(I) = SheetNames(J)
SheetNames(J) = Temp

NoSort:
Next J
Next I

'Move visible sheets into sorted position
For I = SheetCount To 1 Step -1
Sheets(SheetNames(I)).Move Before:=Sheets(OldActive)
Next I

'reactivate the original active sheet
Application.ScreenUpdating = True
Sheets(OldActive).Activate

End Sub

 

[larrydunn]

Here's my attempt to make the slight change needed to make the tabs before the Main sheet the only ones that get sorted.

As this is a highly specialized routine, I made the assumption that the user would call it FROM the workbook being sorted - and from the Main worksheet before which the sorted tabs will be placed. That allows several simplifications.

I assumed also that any hidden sheets could be moved behind the Main sheet. That leaves new sheets to the fore, where they can be easily sorted and positioned in front of the Main sheet.

Once again: this routine must be called from the first sheet that is not to be sorted!

Sub SortSheets()

'this routine sorts the sheets of the active workbook in
'ascending order, putting new sorted sheets at front, hidden sheets at end

Dim SheetNames() As String
Dim I, J As Integer
Dim SheetCount As Integer
Dim OldActive As String
Dim Temp As String

'determine the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count

'store a reference to the active sheet
OldActive = ActiveSheet.Name

'turn off screen updating
Application.ScreenUpdating = False

'Move any hidden sheets after Main sheet
AnyHidden:
I = 0
NextHidden:
I = I + 1
If Sheets(I).Name = OldActive Then GoTo GetSheets 'Reached main sheet
If Sheets(I).Visible = True Then GoTo NextHidden
Sheets(I).Move After:=Sheets(OldActive)
GoTo AnyHidden

GetSheets:
ReDim SheetNames(SheetCount)
I = 0

FillArray:
I = I + 1
Sheets(I).Select
If Sheets(I).Name = OldActive Then GoTo ArrayFilled
SheetNames(I) = ActiveSheet.Name
GoTo FillArray

ArrayFilled:
SheetCount = I - 1 'Sort only visible sheets before Main

For I = 1 To (SheetCount) - 1
For J = (I + 1) To SheetCount
If SheetNames(I) < SheetNames(J) Then GoTo NoSort
Temp = SheetNames(I)
SheetNames(I) = SheetNames(J)
SheetNames(J) = Temp

NoSort:
Next J
Next I

'Move visible sheets into sorted position
For I = 1 To SheetCount
Sheets(SheetNames(I)).Move Before:=Sheets(OldActive)
Next I

'reactivate the original active sheet
Application.ScreenUpdating = True
Sheets(OldActive).Activate

End Sub

 

[zakasnak]

That's an awesome VBA! Thank so much for posting it!

 

[Cbrine]

Just stumbled on this post, and I know it already has a solution, but thought I would post mine as well. I use the build in excel sort capabilities to sort by name, so I don't need to do an array sort.

Dim wb As Workbook, ws As Worksheet, cs As Worksheet, cell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cs In wb.Sheets
    If cs.Name <> ws.Name Then
        ws.Range("A65535").End(xlUp).Offset(1, 0).Value = cs.Name
    End If
Next cs
ws.Range("A1", ws.Range("A65535").End(xlUp)).Sort ws.Range("A2"), xlDescending

For Each cell In ws.Range("A2", ws.Range("A65535").End(xlUp))
    wb.Sheets(cell.Value).Move , Sheets(1)
Next cell

ws.Range("A1", ws.Range("A65535").End(xlUp)).ClearContents
ws.Activate

Just add a button to a blank worksheet you have inserted(This one will not be sorted) and add the code to the button. Exit design mode and press the button.

HTH
Cal

 

[tusharm]

A couple of problems with this.

First, it overwrites the contents of the active sheet!

Second, it will incorrectly handle a name such as 0001 since the ...value=cs.name will cause XL to store the value as the number 1.

I haven't looked at the previous solutions posted, but a common reference for many is Chip Pearson's http://www.cpearson.com/excel/sortws.htm

If more than one sheet is selected, his code automatically sorts only the selected sheets. Otherwise, it sorts all the sheets in the workbook.

On the downside, his sort process leaves something to be desired since it reorganizes the sheets repeatedly.

Just stumbled on this post, and I know it already has a solution, but thought I would post mine as well. I use the build in excel sort capabilities to sort by name, so I don't need to do an array sort.

Dim wb As Workbook, ws As Worksheet, cs As Worksheet, cell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cs In wb.Sheets
    If cs.Name <> ws.Name Then
        ws.Range("A65535").End(xlUp).Offset(1, 0).Value = cs.Name
    End If
Next cs
ws.Range("A1", ws.Range("A65535").End(xlUp)).Sort ws.Range("A2"), xlDescending

For Each cell In ws.Range("A2", ws.Range("A65535").End(xlUp))
    wb.Sheets(cell.Value).Move , Sheets(1)
Next cell

ws.Range("A1", ws.Range("A65535").End(xlUp)).ClearContents
ws.Activate

Just add a button to a blank worksheet you have inserted(This one will not be sorted) and add the code to the button. Exit design mode and press the button.

HTH
Cal

 

[Cbrine]

Tushar,
In response, I believe Chip's most likely come up with a better way. I'm a huge fan of his work.

First point
Overwriting the sheet.

If you read the entire post, I stated INSERT a worksheet and and a button to this worksheet. The activesheet is not included in the sort, and is used as the template for the sort.

Second point
Yes, it will choke on 0001, but take a look at the title of this post. It states alphabatize sheet tabs. 00001 is numeric,(Well, evaluates to numeric) not alpha data. A small change resolves the problem.

Private Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet, cs As Worksheet, cell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cs In wb.Sheets
    If cs.Name <> ws.Name Then
        ws.Range("A65535").End(xlUp).Offset(1, 0).Value = "'" & cs.Name
    End If
Next cs
ws.Range("A1", ws.Range("A65535").End(xlUp)).Sort ws.Range("A2"), xlDescending

For Each cell In ws.Range("A2", ws.Range("A65535").End(xlUp))
    wb.Sheets(cell.Text).Move , Sheets(1)
Next cell

ws.Range("A1", ws.Range("A65535").End(xlUp)).ClearContents
ws.Activate
End Sub

Cal