Archive for the ‘Sorting’ category

How to Sort Sheets in Excel

August 11, 2006

You’re plowing through a project in Excel.  You’re averaging and adding and dividing and returning depreciations like nobody’s business.  You’re inserting new sheets and naming the tabs without even thinking about it.  Suddenly, it occurs to you that maybe, just maybe, it would be cool if those tabs were in alphabetical order.  No sweat, you think to yourself.  I’ll just mosey on over to the Data menu, select Sort and. . .hmmm.  It has to be in here somewhere.  Well, I AM working with sheets, you remind yourself.  Maybe if I just right-click one of these here tabs. . .  Nope, not there either.  You head on over to the Help section, I mean, one can’t be expected to know everything in Excel, right?  Hmmm.  No answer there, either. 

Annoyance starts to set in at this point.  Why on earth, you ask yourself, is it so #&@$ing hard to sort my worksheets?

Newsflash: Excel does not provide an option to sort sheets alphanumerically!  I don’t know what manner of insanity this is, but there is hope.  You can create a macro and sort sheets in ascending or descending or until your heart’s content.

1.  Click Tools –> Macro –> Macros.

2.  Name the Macro (Remember, no spaces! Try SortSheets as a name).

3.  Click the Create button.  This will open the Visual Basic editor.

4.  Highlight the text in the code window.  It should read ‘Sub SortSheets() End Sub’

5.  Copy and replace the highlighted text with the following code:

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

5.  Click File --> Close and return to Microsoft Excel.

Now run your macro by clicking Tools –> Macro –> Macros.  Highlight Sort_Active_Book and click Run.  When prompted, select Yes or No to sort your sheets in ascending or descending order. 

Nifty, huh?

The macro will only be available in the active workbook.  There are ways to create a template, save it to the XLStart menu and have the macro be available for all your workbooks, but you’d have to lower your macro security level or digitally sign your macro.

See the following Microsoft Office Assistance and Knowledge Base (MS KB) articles for my sources and more information:

How to Sort Sheets in a Workbook

How to run the sample code for the Office XP programs from Knowledge Base articles

About Excel templates

Happy sorting from a random broad.

Good luck and happy taking care of things from a random broad.

Add to Google

Advertisements