Sort sheets using both alpha and numeric?

[Guest]

I need a function that I can call to sort sheets in ascending order left to
right, but NOT using DOS logic. I need conventional sort order. For example,
the following sheet names would be sorted as follows using a regular sort
routine:

1 Sheet
2 Sheet
20 Sheet
200 Sheet
3 Sheet
Sheet 6
Sheet 60
Sheet 700
Sheet 8

I need them sorted alpha and numeric; numbers may be at the beginning,
middle or end of the sheet name. Can someone please help by posting a
function that will handle this? Thanks much in advance.

 

[Guest]

You will need to do a sort of some kind in code. Try goggle for bubble sort
or quick sort.

To get a list of sheets in to an array here is some code:

Sub Temp()

Dim aSheets() As String '*** Array to store sheet names
Dim sht As Worksheet, iLoop As Integer

ReDim aSheets(1 To ThisWorkbook.Sheets.Count)
iLoop = 1

'*** Loop thru each sheet and store the name
For Each sht In ThisWorkbook.Worksheets
aSheets(iLoop) = sht.Name

iLoop = iLoop + 1
Next sht

'*** Do bubble sort or quick sort on the array

Set sht = Nothing

End Sub


Regards,

Stewart

 

[Jim Cone]

I have two ways to sort sheets...

"Numeric"
1 Sheet, 2 Sheet, 3 Sheet , 20 sheet, 200 Sheet, Sheet 6, Sheet 8, Sheet 60, Sheet 700

"Conventional?"
1 Sheet, 2 Sheet, 20 sheet, 200 Sheet, 3 Sheet, Sheet 6, Sheet 60, Sheet 700, Sheet 8

The sheet sorting is part of my Excel add-in "XL Extras".
It is accomplished by clicking on a pop-up menu item.

The updated version of the add-in, which includes several additional features,
will be available shortly - release 1.15
Available - free - upon direct request, remove XXX from my email address.

Jim Cone
San Francisco, USA
(e-mail address removed)