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:

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

Test the code:

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

Sample File:

sortsheets.zip 6.93KB 

Approved by mdmackillop


This entry has been viewed 519 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express