Sorting Worksheets Numerically

[jnf40]

I have tried Chip Pearson's sorting code but it doesn't work on my
worksheets. I think his code has to have something more than just numbers. I
have the worksheet names formatted as follows...

mylinenum = Range("linenum")
If Range("linenum").Value < 1000 Then
mylinenum = Format(mylinenum, "000")
ElseIf Range("linenum").Value > 999 Then
mylinenum = Format(mylinenum, "0000")
End If

this works fine to name the worksheets 005, 010, 015 etc. But it will not
sort the worksheets. Can this be done using numbers only for the worksheet
names after I have formatted the worksheet names this way? The following is
the sort code...

Sub SortTheSheets()

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 CInt(Mid(Worksheets(N).Name, 6)) > _
CInt(Mid(Worksheets(M).Name, 6)) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If CInt(Mid(Worksheets(N).Name, 6)) < _
CInt(Mid(Worksheets(M).Name, 6)) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M
End Sub

[Gary Keramidas]

does this version of cp's code sort correctly?

Sub AlphaSortWorksheets()

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

SortDescending = False

FirstWSToSort = 1
LastWSToSort = Worksheets.Count

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

--

Gary

"jnf40" <jn...@discussions.microsoft.com> wrote in message
news:A7FC0724-1228-41A9...@microsoft.com...

[jnf40]

No, I had the AlphaSort code in first.

[Jim Cone]

You can try the free Excel add-in "Excel Extras".
By default It sorts sheets in true numerical order.
(It can also use the standard MS sort order where 10 comes before 2.)
It does other nice stuff like creating a linked Table of Contents sheet.
Download from ... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA

.

"jnf40" <jn...@discussions.microsoft.com> wrote in message news:A7FC0724-1228-41A9...@microsoft.com...

[jnf40]

Jim...I have never used add-ins, I don't guess. If I were to add this on my
computer, when I send the workbook to others would they also need this add-in
on their computer in order for it to work? If this is so then it will not
work.

"Jim Cone" wrote:

> You can try the free Excel add-in "Excel Extras".
> By default It sorts sheets in true numerical order.
> (It can also use the standard MS sort order where 10 comes before 2.)
> It does other nice stuff like creating a linked Table of Contents sheet.
> Download from ... http://www.realezsites.com/bus/primitivesoftware
> No registration required.
> --
> Jim Cone
> San Francisco, USA
>

> ..

[jnf40]

i really need it to sort by code due to the way worksheets are added.

[Jim Cone]

Yes, to use an add-in it must be installed on each computer that
needs it. Most add-ins install very easily... Tools | Add-ins and checkmark
the add-in name (or browse to find it).
If you provide code in your workbook, then other users will have to OK
the use of macros when they open the workbook. You will also have to
provide some way to run the code... Either a button on the worksheet
or an attached toolbar.

--
Jim Cone
San Francisco, USA

http://www.officeletter.com/blink/specialsort.html

"jnf40" <jn...@discussions.microsoft.com>
wrote in message

[jnf40]

They will be in that order...005, 010, 015, but if the user forgot one and
added 007, as the code is now it would be 005, 010, 015, 007...I need it to
be 005, 007, 010, 015.. After the sheets are created and named the code
automatically goes to the sort sub.

"Gary Keramidas" wrote:

> in your example, 005, 010, 015, what order are they supposed to be in after
> they're sorted?
>
> --
>
>
> Gary

>
>
> "jnf40" <jn...@discussions.microsoft.com> wrote in message

> news:A7FC0724-1228-41A9...@microsoft.com...

[Gary Keramidas]

i added 007 after 015 so the order was 005,010,015,007.

when i ran the code i posted, the sheets were in this order:
005,007,010,015

so, i guess i don't see what you do or i am missing something

--

Gary

"jnf40" <jn...@discussions.microsoft.com> wrote in message

news:3BAD2D04-6278-4073...@microsoft.com...

[Gary Keramidas]

in your example, 005, 010, 015, what order are they supposed to be in after
they're sorted?

--

Gary

"jnf40" <jn...@discussions.microsoft.com> wrote in message
news:A7FC0724-1228-41A9...@microsoft.com...

[jnf40]

well I'll try it again and let you know.