Sorting sheets in workbook

[Guest]

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

 

[Chip Pearson]

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

 

[Chip Pearson]

I just updated the web page to include this code.


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

 

[Guest]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Jim Cone]

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


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