How to Sort/Alphabetize Worksheets by Name

[Chaplain Doug]

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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

[JE McGimpsey]

one way:

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


In article <2D5D86A1-383A-41ED-BB74-0582A53F4502@microsoft.com>,
"Chaplain Doug" <ChaplainDoug@discussions.microsoft.com> wrote:

> 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.

[Jim Thomlinson]

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
"Chaplain Doug" wrote:

> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

[Chaplain Doug]

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!

"Chaplain Doug" wrote:

> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org