Sorting TABS

I have created a spreadsheet with over 600 sheets all individually numbered.

I have managed to create a macro and button to click and navigate to/from sheet BUT when adding/deleting/renaming individual sheets, I still have to drag each one (or at least in groups) along the tab list to put them into numerical order.

Is there anyway I can do this other than manually? There may also be gaps in the sequence...ie they may go...524, 550, 551, 552, 553, 605, 606, 3982 etc

Thanks

Andrew

Thanks for both replies...do these both work automatically or would i need to run a macro manually?

Sorry, Im ok but not great with macros/VB

Thanks for that Alan
The one from Richard gave an Error 13 runtime error...dont know if its something Ive done
Yours has sorted them but instead of starting at 500 and then 501, 502, 542, 600, 602 up to 3982, 3991, 4540, 5289 etc its placing them in 3982, 3991, 4540, 500, 501, 502, 5289, 542, 600, 602 etc
Any thoughts or is it still me doing something wrong

Instead of starting at 500 and then 501, 502, 542, 600, 602 up to 3982, 3991, 4540, 5289 etc its placing them in 3982, 3991, 4540, 500, 501, 502, 5289, 542, 600, 602 etc

Excel treats all your sheet names as strings, not numbers. So a string starting with 3 is sorted before a string starting with 5.

Two ways to solve your problem are:
a. Permanently rename all 3 digit Tab names to include a leading ZERO, all 2 digit tab names to include 2 leading ZEROS. Sort.
b. Temporarily rename all 3 digit Tab names to include a leading ZERO, all 2 digit tab names to include 2 leading ZEROS. Sort. Remove leading ZEROs from Tab names.

Lewis

Try using this code to sort all of the sheets works for sheets which are numbers or text:

Please Login or Register  to view this content.

If you can install add-ins, I love the navigation pane included in kutools for excel. Makes it very easy to navigate through files with lots of tabs and allows you to sort them any way you'd like.

http://www.extendoffice.com/product/...xcel-list.html

Hi...sorry for not getting back sooner!

Thanks for all the help...the macro from Cerbera seems to have done the job I wanted

Richard: yes, I did have a couple of sheets with alpha names...sorry I didn't explain that in the original message

Thanks again to all that replied

Andrew