excel 2007 - how do I sort workspaces in alphabetical order

I have an excel spreadsheet with lots of different workspaces for differing topic but there are a lot of workspaces now and not in alphabetical order. How can this be done automatically.
gpersandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Please upload an example workbook, so the experts can more readily assist.  Ensure its non-sensitive data, obfuscate the data if you need to.  There should be a framework from which to develop a solution, as your question is too broad.

Cheers,

Dave
0
gpersandAuthor Commented:
excel
I think iv over confused things. All I need are the sheets at the bottom of the page in alphabetical order. The picture above is in the order sheet1,sheet2 and Asheet3 which should be first. Hope that clears things up
0
gowflowCommented:
Try this code depending on how you reply Yes will sort ascending no will sort descending all sheets in the workbook based on hte sheet name.
rgds/gowflow
Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For I = 1 To Sheets.count
      For J = 1 To Sheets.count - 1
'
' If the answer is Yes, then sort in ascending order.
'
         If iAnswer = vbYes Then
            If UCase$(Sheets(J).Name) > UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(J).Name) < UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
         End If
      Next J
   Next I
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

gowflowCommented:
For your easy refrence here is a workbook with the button in sheet1 and the code above that give you an example on how to implement in your workbook.
gowflow
Sort-Sheets.xls
0
gpersandAuthor Commented:
Sorry but im confused as to how to use this code on my spreadsheet? Please advise.
0
gowflowCommented:
ok fine
Are you using Excel 2007 or Excel 2003 ? as it is diffrent for each
gowflow
0
gpersandAuthor Commented:
excel 2007
0
gowflowCommented:
1) open blank Excel 2007 and press on the upper office icon then choose Excel options.
2) It will open the Popular Tab on the left by default lookk on the right pane the 3rd item Show Developper Tab in the ribbon by default it is not ticked make sure you tick it
3) press on Trust Center in the left tab then on the right pane Trust Center Settings then from the left pane choose Macro Settings it will display in the right pane choices choose the second option Disable All macros with notification. Press Ok twice and exit Excel
4) Open your workbook
5) select File/Save as and choose in the right pane Macro Enabled Workbook it will open a saveas window with the name of your workbook but with an extention .xlsm (your present workbook has an extention .xlsx) press ok from now on you should use the workbook with the extention .xlsm exit the workbook and make sure you put away the workbook with the .xlsx extention so you do not update it by mistake.
6) open the newly created workbook with .xlsm extention
7) you will see your data there and if you look at the menus you will see the one before the end is called Developper.
8) Click on Developper menu and then click on the first icon Called Visual Basic
9) a window will open displayin on the left pane your workbook name underneith which all your sheets and the last item is called Thisworkbook.
10) click on your workbook name and right click choose insert and select module
11) you will see module1 has been created
12) doubleclik on module1
13) press on the button Select ALL from the below code and in the code right click then choose copy
14) click in the right pane in your workbook in module1 and paste the code there
15) save the workbook exit it

Now to test the workbook
1) open it the version we just saved
2) you will see under the ribbon Macroes have been disabled then a button called options click on it and select enable then ok
3) You may insert a button (i can tell you later if you want) or for now simply click on Developper menu and choose the second icon to the left Macros it will display the macro Sort_Active_Book select it and press Run

Check results and advise your comments.
gowflow
Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For I = 1 To Sheets.count
      For J = 1 To Sheets.count - 1
'
' If the answer is Yes, then sort in ascending order.
'
         If iAnswer = vbYes Then
            If UCase$(Sheets(J).Name) > UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(J).Name) < UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
         End If
      Next J
   Next I
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.