Sort Certain Worksheets in Workbook

[snsd]

Hi:

Dave Peterson helped me put together the following macro that sorts al
the worksheets in my workbook *starting with* the 20th sheet. Because
keep adding sheets to the workbook, I'm constantly having to change th
"FirstWStoSort" value (see below) to make sure that certain sheets don'
get sorted. I have decided to insert a "dummy" worksheet calle
"aaaDoNotDelete" before the first sheet that I want included in th
sort. What do I have to do to change the macro so that instead of th
sorting beginning after a specfied *number * of worksheets, it no
starts after a *specific * worksheet - namely "aaaDoNotDelete". I'
assuming that's a "no brainer" task but I'm a relative newbie to macro
in general.

Thanks in advance for your help.

Dave

Sub zzSortWorksheetsAlphabetically()

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 = 19* 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
End Su

 

[Tom Ogilvy]

Sub zzSortWorksheetsAlphabetically()

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 = 19*
FirstWSToSort = Sheets("aaaDoNotDelete").Index + 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
End Sub