Sorting sheets in workbook

I have a workbook that has sheets in it that are sheet1 – sheet33. I tried to
use the code from http://cpearson.com/excel/sortws.htm The problem is
that it does not handle the numbers in the sheet in the right order. It puts
them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to Sheet2. Any ideas?
Thanks! Jeff

Jeff,

Try

Sub SortWorksheets()

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





--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
>I have a workbook that has sheets in it that are sheet1 -
>sheet33. I tried to
> use the code from http://cpearson.com/excel/sortws.htm The
> problem is
> that it does not handle the numbers in the sheet in the right
> order. It puts
> them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
> Sheet2. Any ideas?
> Thanks! Jeff

I just updated the web page to include this code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Chip Pearson" <chip@cpearson.com> wrote in message
news:eonVCb6QGHA.5908@TK2MSFTNGP14.phx.gbl...
> Jeff,
>
> Try
>
> Sub SortWorksheets()
>
> 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
>
>
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
>>I have a workbook that has sheets in it that are sheet1 -
>>sheet33. I tried to
>> use the code from http://cpearson.com/excel/sortws.htm
>> The problem is
>> that it does not handle the numbers in the sheet in the right
>> order. It puts
>> them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
>> Sheet2. Any ideas?
>> Thanks! Jeff
>
>

That is excellent, you guys are amazing. Thanks! Jeff

"Chip Pearson" wrote:

> Jeff,
>
> Try
>
> Sub SortWorksheets()
>
> 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
>
>
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
> >I have a workbook that has sheets in it that are sheet1 -
> >sheet33. I tried to
> > use the code from http://cpearson.com/excel/sortws.htm The
> > problem is
> > that it does not handle the numbers in the sheet in the right
> > order. It puts
> > them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
> > Sheet2. Any ideas?
> > Thanks! Jeff
>
>
>

I have a different workbook that the sheet are not named Sheet1 - Sheet 33
but it is named Red1 - Red15. The code errors out if they are not named
Sheet. Any Ideas? Thanks! Jeff

"Chip Pearson" wrote:

> Jeff,
>
> Try
>
> Sub SortWorksheets()
>
> 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
>
>
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
> >I have a workbook that has sheets in it that are sheet1 -
> >sheet33. I tried to
> > use the code from http://cpearson.com/excel/sortws.htm The
> > problem is
> > that it does not handle the numbers in the sheet in the right
> > order. It puts
> > them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
> > Sheet2. Any ideas?
> > Thanks! Jeff
>
>
>

Change the

Mid(Worksheets(N).Name, 6)

to

Mid(Worksheets(N).Name, 4)

in all four instances. The number in the Mid statement should be
the character position of the first number in the worksheet name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:9F3B7EEC-59E0-470A-A1FF-361B358D4BBA@microsoft.com...
>I have a different workbook that the sheet are not named
>Sheet1 - Sheet 33
> but it is named Red1 - Red15. The code errors out if they are
> not named
> Sheet. Any Ideas? Thanks! Jeff
>
> "Chip Pearson" wrote:
>
>> Jeff,
>>
>> Try
>>
>> Sub SortWorksheets()
>>
>> 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
>>
>>
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
>> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
>> >I have a workbook that has sheets in it that are sheet1 -
>> >sheet33. I tried to
>> > use the code from http://cpearson.com/excel/sortws.htm
>> > The
>> > problem is
>> > that it does not handle the numbers in the sheet in the
>> > right
>> > order. It puts
>> > them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
>> > Sheet2. Any ideas?
>> > Thanks! Jeff
>>
>>
>>

Is there a way to have different length sheet names like Sheet1 - Sheet20 and
Red1 - Red15 in the same workbook and sort them all alphabetically first then
numerically? Thanks! Jeff

"Chip Pearson" wrote:

> Change the
>
> Mid(Worksheets(N).Name, 6)
>
> to
>
> Mid(Worksheets(N).Name, 4)
>
> in all four instances. The number in the Mid statement should be
> the character position of the first number in the worksheet name.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:9F3B7EEC-59E0-470A-A1FF-361B358D4BBA@microsoft.com...
> >I have a different workbook that the sheet are not named
> >Sheet1 - Sheet 33
> > but it is named Red1 - Red15. The code errors out if they are
> > not named
> > Sheet. Any Ideas? Thanks! Jeff
> >
> > "Chip Pearson" wrote:
> >
> >> Jeff,
> >>
> >> Try
> >>
> >> Sub SortWorksheets()
> >>
> >> 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
> >>
> >>
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> >> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
> >> >I have a workbook that has sheets in it that are sheet1 -
> >> >sheet33. I tried to
> >> > use the code from http://cpearson.com/excel/sortws.htm
> >> > The
> >> > problem is
> >> > that it does not handle the numbers in the sheet in the
> >> > right
> >> > order. It puts
> >> > them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
> >> > Sheet2. Any ideas?
> >> > Thanks! Jeff
> >>
> >>
> >>
>
>
>

My free Excel add-in "Excel Extras" will do that as well as
other stuff including inserting a table of contents and
providing several text case options. Download here...
http://www.realezsites.com/bus/primitivesoftware
Jim Cone
San Francisco, USA


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
Is there a way to have different length sheet names like Sheet1 - Sheet20 and
Red1 - Red15 in the same workbook and sort them all alphabetically first then
numerically? Thanks! Jeff



"Chip Pearson" wrote:
> Change the
> Mid(Worksheets(N).Name, 6)
> to
> Mid(Worksheets(N).Name, 4)
> in all four instances. The number in the Mid statement should be
> the character position of the first number in the worksheet name.
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:9F3B7EEC-59E0-470A-A1FF-361B358D4BBA@microsoft.com...
> >I have a different workbook that the sheet are not named
> >Sheet1 - Sheet 33
> > but it is named Red1 - Red15. The code errors out if they are
> > not named
> > Sheet. Any Ideas? Thanks! Jeff
> >
> > "Chip Pearson" wrote:
> >
> >> Jeff,
> >>
> >> Try
> >>
> >> Sub SortWorksheets()
> >>
> >> 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
> >>
> >>
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> >> news:B0FAC482-61A6-484B-8B9A-B434814C81C1@microsoft.com...
> >> >I have a workbook that has sheets in it that are sheet1 -
> >> >sheet33. I tried to
> >> > use the code from http://cpearson.com/excel/sortws.htm
> >> > The
> >> > problem is
> >> > that it does not handle the numbers in the sheet in the
> >> > right
> >> > order. It puts
> >> > them Sheet1, Sheet10, Sheet11, Sheet12...before it gets to
> >> > Sheet2. Any ideas?
> >> > Thanks! Jeff
> >>
> >>
> >>
>
>
>