Sort worksheets by tab name?

  • Thread starter Thread starter Bill B
  • Start date Start date
B

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?
 
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
 
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
 
Back
Top