How to Sort/Alphabetize Worksheets by Name

[Guest]

Excel 2003. I am programmatically creating a master workbook from multiple
workbooks. When I am done I would like to arrange the worksheets in the
master workbook in alphabetical order, according to the worsheet names. How
may this be done? Thanks.

 

[JE McGimpsey]

one way:

http://cpearson.com/excel/sortws.htm

 

[Guest]

Public Sub SortWorksheets()
On Error Resume Next
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

Application.ScreenUpdating = False
SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

Application.ScreenUpdating = False
End Sub

HTH

 

[Guest]

This is what I used to accomplish the sheet sort:

'Now sort the sheets in the master workbook by name
'Use a bubble sort
With WbNew
JFound = True: J = 0
While JFound = True
DoEvents
JFound = False
For I = 1 To .Sheets.Count - 1
DoEvents
If .Worksheets(I + 1).Name < .Worksheets(I).Name Then
.Worksheets(I + 1).Move before:=.Worksheets(I)
JFound = True
End If
Next I
J = J + 1
Wend
End With

Thanks for the help!