Excel

Excel sheet sorting

Ease of Use

Easy

Version tested with

2000 

Submitted by:

MWE

Description:

xlSortSheets will sort all sheets in the active workbook in either ascending (A -> Z) or descending (Z -> A) order 

Discussion:

Keeping track of just a few sheets in Excel is easy. But as the number of sheets grows, some method of ordering the sheets can be helpful. xlSortSheets will sort all sheets in the active workbook in either ascending or descending order. Ascending is the default. 

Code:

instructions for use

			

Option Explicit Sub xlSortSheets_Test() Dim strWhich As String Dim Which As Integer strWhich = InputBox("enter sort direction: 1 = A -> Z; -1 = Z -> A", _ "Demo of xlSortSheets", 1) If strWhich = vbNullString Then Exit Sub If strWhich = "-1" Or strWhich = "1" Then Which = strWhich Call xlSortSheets(Which) Exit Sub End If MsgBox "only values of -1 and 1 are valid" & vbCrLf & _ "no sorting done.", vbOKOnly End Sub Sub xlSortSheets(Optional Which As Integer = 1) ' '**************************************************************************************** ' Function: sorts sheets in active workbook alphbetically ' Passed Values: ' Which [Optional, Input, Integer] sorting direction: ' 1 ==> A -> Z ' -1 ==> Z -> A ' '**************************************************************************************** ' Dim I As Integer Dim J As Integer Dim SheetNames() As String Dim temp As String ' ' store sheet names ' Redim SheetNames(Sheets.Count) For I = 1 To Sheets.Count SheetNames(I) = Sheets(I).Name Next I ' ' sort sheet names via simple bubble sort ' For I = 1 To Sheets.Count - 1 For J = I + 1 To Sheets.Count If (Which = -1 And SheetNames(I) < SheetNames(J)) _ Or _ (Which = 1 And SheetNames(I) > SheetNames(J)) Then temp = SheetNames(I) SheetNames(I) = SheetNames(J) SheetNames(J) = temp End If Next J Next I ' ' alphabetize sheets ' temp = Sheets(Sheets.Count).Name For I = Sheets.Count To 1 Step -1 Sheets(SheetNames(I)).Select Sheets(SheetNames(I)).Move Before:=Sheets(temp) temp = SheetNames(I) Next I End Sub

How to use:

  1. Open the example. The example contains several worksheets. There is no useful data in any of the sheets; they are provided to demonstrate the sheet sorting procedure. From any sheet, navigate to Tools | Macro | Macros (or Alt+F8)Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
  9. and double-click on xlSortSheets_Test. The user will be asked to specify either 1 for ascending sort or ?1 for descending. The test procedure will call xlSortSheets.
 

Test the code:

  1. Open the example. The example contains several worksheets. There is no useful data in any of the sheets; they are provided to demonstrate the sheet sorting procedure. From any sheet, navigate to Tools | Macro | Macros (or Alt+F8) and double-click on xlSortSheets_Test. The user will be asked to specify either 1 for ascending sort or ?1 for descending. The test procedure will call xlSortSheets.
 

Sample File:

SheetSort.zip 19.86KB 

Approved by mdmackillop


This entry has been viewed 292 times.

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