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

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.

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

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