VBA Express : Excel - Sort Worksheets Alphabetically

Excel

Sort Worksheets Alphabetically

Ease of Use

Easy

Version tested with

2000

Submitted by:

Anne Troy

Description:

Sorts all worksheets in your workbook alphabetically by sheet name.

Discussion:

By Chip Pearson, Microsoft Excel MVP, of www.cpearson.com. You may receive an Excel file from your home office each week with one worksheet for each salesperson. The worksheets may be in employee number order, or no particular order at all, and you would always like to place them in alphabetical order. Here's your tool. You can skip the first worksheet or two or three by changing the FirstWSToSort number to the index number of the first worksheet you want to sort. The code below starts with the first worksheet, but our sample file starts with the second.

Code:

instructions for use

			Option Explicit 
 
Sub SortWorksheets() 
     
    Dim N As Integer 
    Dim M As Integer 
    Dim FirstWSToSort As Integer 
    Dim LastWSToSort As Integer 
    Dim SortDescending As Boolean 
     
    SortDescending = False 
     
    If ActiveWindow.SelectedSheets.Count = 1 Then 
         
         'Change the 1 to the worksheet you want sorted first
        FirstWSToSort = 1 
        LastWSToSort = Worksheets.Count 
    Else 
        With ActiveWindow.SelectedSheets 
            For N = 2 To .Count 
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then 
                    MsgBox "You cannot sort non-adjacent sheets" 
                    Exit Sub 
                End If 
            Next N 
            FirstWSToSort = .Item(1).Index 
            LastWSToSort = .Item(.Count).Index 
        End With 
    End If 
     
    For M = FirstWSToSort To LastWSToSort 
        For N = M To LastWSToSort 
            If SortDescending = True Then 
                If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then 
                    Worksheets(N).Move Before:=Worksheets(M) 
                End If 
            Else 
                If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then 
                    Worksheets(N).Move Before:=Worksheets(M) 
                End If 
            End If 
        Next N 
    Next M 
     
End Sub

How to use:

Copy the code above.
Open the Excel file in which you want to use the code.
Hit Alt+F11 to open the Visual Basic Editor (VBE) window.
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Save the file and close the VBE.

Test the code:

Hit Tools-Macro-Macros and double-click SortWorksheets.

Sample File:

sortsheets.zip 6.93KB

Approved by mdmackillop

This entry has been viewed 525 times.