Sort worksheets by tab name?

[Bill B]

I have 100+ worksheets within one workbook. (The tabs are
named "Test 1", "Test 2", up to "Test 100").

Of course, when I receive the Excel file, the tabs are in
the following order:

Test 1
Test 10
Test 11...
Test 19
Test 100
Test 101....
Test 119
Test 2 and so on.

I need to be able to sort the worksheets by the name on
the tab.

Does anyone know how to do this?

 

[Peo Sjoblom]

One possible way

http://www.cpearson.com/excel/sortws.htm

 

[Bill]

Thank you for that information. The macro works well if
the tab name is alpha characters. It does not work as
well for numeric names. I should have been more specific
with my request.

I have looked at the code to try to modify it for
numerical sorting. I suspect the tab names are "string"
type data. Unfortunately I have very limited VB skills
and haven't found a solution.

Any suggestions?


Bill

 

[Peo Sjoblom]

Works for me using numbers

 

[Chip Pearson]

Bill,

If all your worksheet names are numeric, you can get a numeric
sort by changing the lines of code in my code from

If UCase(Worksheets(n).Name) > UCase(Worksheets(m).Name) Then
to
If Val(Worksheets(n).Name) > Val(Worksheets(m).Name) Then

and

If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
to
If Val(Worksheets(N).Name) < Val(Worksheets(M).Name) Then


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