Sorting Worksheet Tabs

[Craig]

I'm using Excel 2003.

I've searched this site and have found sorting tips for worksheet tabs
(mainly Chip Pearson's VBA code).

However, this ONLY seems to work by assuming the named tabs are ALPHA. What
if all 100 of my tabs are numbers???

EXAMPLE:

If I have the following tabs labeled: 1, 12, 21, 2000, 350, 3100, 55, 520,
1120, 2500

...then after running the code I will get the following INCORRECT result:

1, 1120, 12, 2000, 21, 2500, 3100, 350, 520, 55

Is there a way around this? How do I do a NUMERIC sort??

Thanks for any and all help.

 

[Mike H]

Graig,

Try this

Sub Sortem()
LastSheet = Sheets.Count
On Error Resume Next
For ws = 1 To LastSheet
For ws2 = ws To LastSheet
If CLng(Sheets(ws2).Name) < CLng(Sheets(ws).Name) Then
Sheets(ws2).Move Before:=Sheets(ws)
End If
Next ws2
Next ws
End Sub


Mike

 

[Craig]

BRILLIANT!!! Worked great! Thanks.

--
Craig

Graig,

Try this

Sub Sortem()
LastSheet = Sheets.Count
On Error Resume Next
For ws = 1 To LastSheet
For ws2 = ws To LastSheet
If CLng(Sheets(ws2).Name) < CLng(Sheets(ws).Name) Then
Sheets(ws2).Move Before:=Sheets(ws)
End If
Next ws2
Next ws
End Sub


Mike

 

[Mike H]

Glad I could help

BRILLIANT!!! Worked great! Thanks.

 

[Ron de Bruin]

See also
http://www.contextures.com/xlToolbar01.html

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Craig]

Great...thanks Ron for the tip.

--
Craig

See also
http://www.contextures.com/xlToolbar01.html

 

[Chip Pearson]

I've updated the code on the web page to allow for sorting numeric
sheet names in numeric or text order.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sri]

I've updated the code on the web page to allow for sorting numeric
sheet names in numeric or text order.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)









- Show quoted text -

I like the Pearson code. Covers things in all angles. My code is not
that extensive with all the error handling etc., it very simple but
works for alpha, alphanumeric and numeric ascending sorting of
worksheet tabs. If interested just take a look at the article.
http://funwithexcel.blogspot.com/2009/04/macro-to-sort-worksheets-tabs-in-excel.html