ExcelBanter - Change Excel sheet order

courtesy of chip pearson

Sub SortWorksheets()
'Chip Pearson

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

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

End Sub

--
Gary''s Student

"KeithS" wrote:

Excel 97.
I'm cataloging my CD's - each CD details on one sheet, then I've
renamed the sheet to the CD number) which is a 7 digit alpha/numeric
format).
I'd like to rearrange the sheets into alpha/numeric order. Is there
any way to achieve this easily, please?

--
KeithS.

To reply directly, replace the first at with the second one