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.

You could use a macro...

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

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

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

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

"Dave Peterson" wrote:

You could use a macro...

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

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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 wrote:

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

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
ToolsMacros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?
--
DanaK


"Heather" wrote:

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.

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 wrote:

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
ToolsMacros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?
--
DanaK

"Heather" wrote:

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

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!
--
DanaK


"Dave Peterson" wrote:

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 wrote:

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
ToolsMacros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?
--
DanaK

"Heather" wrote:

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

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 wrote:

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!
--
DanaK

"Dave Peterson" wrote:

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 wrote:

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
ToolsMacros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?
--
DanaK

"Heather" wrote:

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


--

Dave Peterson

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!!

--
DanaK


"Dave Peterson" wrote:

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 wrote:

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!
--
DanaK

"Dave Peterson" wrote:

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 wrote:

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
ToolsMacros list,but shows up in my Project explorer. I'm not sure how to
handle Public Function code.....?
--
DanaK

"Heather" wrote:

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


--

Dave Peterson