is it possible to alphabetize tabs and how?

[Heather]

i have over 70 tabs created in an Excel file all named with surnames, is
there a way to sort them into an alphabetical order. I have tried searching
in different areas to see if it can be done but no luck so far.

 

[Dave Peterson]

You could use a macro...

Chip Pearson's:
http://www.cpearson.com/excel/sortws.htm

David McRitchie's:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Heather]

Thanks Dave, wasnt' sure how to execute but initially recorded a macro with
name etc, stopped macro then went to edit the macro. for ease i copied and
pasted the information to add then ran the macro. worked a treat.

Heather

 

[DanaK]

I had the same need as Heather. I copied the code from Chips site & pasted it
into a module in my PERSONAL.XLS. How do I run it? It doesn't show in the
Tools>Macros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?

 

[Dave Peterson]

Did you put the code in a General module in your personal.xls's project?

Did you mark the subroutine Private?
Like:
Private Sub ProcToSort()

Did you mark the module Private?
Option Private Module
(at the top of the module)

 

[DanaK]

Dave- I did put the code into a General module. I did NOT mark the subroutine
Private ( : * not sure what that means!) & ditto for mark the module Private.
I tried from your examples, but it didn't work. I must not be putting it in
the right place. The code begins with :

Public Function SortWorksheetsByName(ByVal FirstToSort As Long, ByVal
LastToSort As Long, _
ByRef ErrorText As String, Optional ByVal SortDescending As Boolean =
False) As Boolean

And then lots of other code....How do I know where/what to put? It's STILL
not showing up anywhere in my macros lists.

Thanks!

 

[Dave Peterson]

Functions don't show up in that dialog--only Subs.

So you can create a subroutine that sorts your worksheets:

Option Explicit
sub SortAllWorksheets()
dim ok as boolean
ok = sortworksheetsbyname(firsttosort:=1, _
lasttosort:=activeworkbook.sheets.count, _
errortext:="Error", _
sortdescending:=false)
msgbox "ok"
end sub

Marking the routine private just means that you have the word "Private" in front
of the first line:

Private Sub SortAllWorksheets()
or
Private Function sortworksheetsbyname(...)

But you'll notice that Chip marked his function Public.

 

[DanaK]

Dave- Thanks for the lesson! The sub routine allows me to run
SortWorksheetsByName so that worked. Unfortunately I get a compile error- Sub
or Function not defined at this line-> B = TestFirstLastSort(FirstToSort,
LastToSort, ErrorText)
I copied the code straight off Chips site, but I'll go back & try it again.
Thanks again!!

 

[Dave Peterson]

Chip had a lot of code on that page. Remember to grab the associated functions,
too.

Dave- Thanks for the lesson! The sub routine allows me to run
SortWorksheetsByName so that worked. Unfortunately I get a compile error- Sub
or Function not defined at this line-> B = TestFirstLastSort(FirstToSort,
LastToSort, ErrorText)
I copied the code straight off Chips site, but I'll go back & try it again.
Thanks again!!