?
Solved

excel 2007 - how do I sort workspaces in alphabetical order

Posted on 2011-10-06
8
Medium Priority
?
410 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:gpersand
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36922979
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
 

Author Comment

by:gpersand
ID: 36923125
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36923279
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 31

Expert Comment

by:gowflow
ID: 36923297
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
 

Author Comment

by:gpersand
ID: 36923399
Sorry but im confused as to how to use this code on my spreadsheet? Please advise.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36923409
ok fine
Are you using Excel 2007 or Excel 2003 ? as it is diffrent for each
gowflow
0
 

Author Comment

by:gpersand
ID: 36923420
excel 2007
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 36923753
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question