How to Sort/Alphabetize Worksheets by Name
 

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

How to Sort/Alphabetize Worksheets by Name
 

one way:

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


In article ,
"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.

How to Sort/Alphabetize Worksheets by Name
 

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 Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=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

How to Sort/Alphabetize Worksheets by Name
 

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