Unhide sheets, sort sheets and data and rehide

Hello . . . I have a couple functions that I would like to happen on "_beforeclose" event:
1. Sort certain ws by names alphabetically (staring with sheet 36)
a. Only sheets ending with "sd"
b. Some will be hidden, some not - (I believe we have to unhide any
that are still hidden first?)
2. Sort the data on the ws alphabetically using col A and ascending
a. There are headers on all sheets
3. Re-hide all the sheets except "order"

I have stolen, begged and borrowed some code to get this far: (no laughing out I'd rather offer up something than seem ungrateful and not even try.)

Please Login or Register  to view this content.

It seems redundant in some parts (and it doesn't work past unhiding!) I'm not sure how to clean it up. Thanks in advance!!

Hi

A different approach.

Please Login or Register  to view this content.

rylo

Hi rylo, Thanks for helping. Sorry it took me so long to get to it. (Darn internet service down again.) The code works wonderfully on all but sorting the worksheet names alphabetically. Just to make sure I was explaining well enough: On "close" the worksheets (starting with sheet #36) and those that end with "sd", are sorted by name a-z. These worksheets are displayed in a listbox later in code and I would like them to be alphabetized when the user sees them in the listbox. (Thanks for your hard work. I need to learn how to use arrays a little more.)

Hi

The sorting worked OK on my dummy file, so maybe it is something to do with the naming convention I used for my testing or perhaps the sorting when everything is made ucase.

Can you please put up an example workbook with an unsorted list of sheets, and detail how these sheets should be sorted at the end.

rylo

Thanks again, rylo. The worksheets are not alpha sorted in the list box and I am getting a code error on this line:

Please Login or Register  to view this content.

I made a mock-up that might help. And again I truly appreciate you hanging with me on this.

Mockorder.xls

Hi

The example workbook has only got 6 sheets, so the macro is erroring due to an invalid size of the array. You do specify that you are only actioning sheets after sheet 36.

Can you please update your example file to be more representative of your real situation.

If you are likely to have less than 36 sheets in the workbook, can you please advise what to do in that scenario.

rylo