Sort just the sheet tabs with numbers on them.

[leaning]

Hello!

I found shg's code and I am trying to adapt it to my workbook.

Each workbook has a sheet named "Cover" and another labelled "TOC" and the other sheets have a mix of numbers and letters ("4500181 sn1", "4500181 sn2", "4500183", etc.).

I want to leave the "Cover" and "TOC" and then sort the other sheets that have number (or number/letter) names.

Here's the code I am working with:

Please Login or Register  to view this content.

Anyone have any ideas?

Respectfully,

Lost

[MarvinP]

Try changing the line of

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Did that help?

[leaning]

MarvinP,

Workbook attached.

The Cover and TOC always wind up at the right rather than the left.

??

Lost

[MarvinP]

Add these two lines at the end of the routine.

Please Login or Register  to view this content.

)

To discover the above lines of code:
Create a blank workbook and add a few worksheets.
Rename the last two tabs as TOC and Cover
Turn on Macro Recorder
Drag TOC tab to left
Drag Cover tab to left
Turn off Recorder and look at the code.

The above two lines seem to do what you wanted. To learn VBA simply turn on the Macro Recorder and do what you think you want. Then read the code that has been recorded.

Hope this helps you to learn.

[leaning]

MarvinP,

The code gets caught in a crazy loop and you have to CTRL-ALT-DELETE it to stop it. I think it is continually sorting the sheets.



Lost

Please Login or Register  to view this content.

[MarvinP]

Hey Learning,

I set a breakpoint in the newest code above. From the immediate window did a Call SortSheets and stepped throught he code. It jumped back to code behind "ThisWorkbook" that was event code.

It looks like you had event code behind ThisWorkbook that was triggered each time through the code so it never ended. Perhaps you could have turned it off using an Application.EnableEvent line but I simply removed the code and it seems to work fine.

Find the attached.

[leaning]

MarvinP,

1. Workbook with 4 tabs in this order:

1000, 101, Cover, TOC

2. Run the code.

3. It sorts them like this:

Cover, TOC, 1000, 101

I am Googling to see if I can find better code.

Lost

[leaning]

MarvinP,

I found one that seems to work great!

Please Login or Register  to view this content.

Thanks for your help with this thread!

Respectfully,

Lost