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

--
sns
-----------------------------------------------------------------------
snsd's Profile: http://www.excelforum.com/member.php...fo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=32020

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

--
Regards,
Tom Ogilvy


"snsd" wrote in message
...

Hi:

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


--
snsd
------------------------------------------------------------------------
snsd's Profile:
http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=320205