Sort your Excel worksheet tabs automatically — even dates!

 

by Kara Hiltz

Application: Microsoft Excel 2000/2002/2003/2004/2007/2010/2013/2016

Operating Systems: Microsoft Windows, Macintosh

Download: http://download.elijournals.com/excel/201808/wrkshtsort.zip

You can sort your worksheet data in a number of ways, but there’s no built-in way to sort your Excel worksheets. You have to manually click and drag them to get them in order, and if you have dozens of worksheets in your workbook, this can be time-consuming and frustrating. To solve this dilemma, use our four macros, which allow you to sort both text-based and date-based worksheet tabs in ascending or descending order.

 

To sort our worksheets without any clicking and dragging, we’ll:

  • Enter code that will sort text-based worksheet tabs in either ascending or descending order.
  • Modify the first set of macros so you can sort worksheet names in chronological order.
  • Adjust our macro so that it groups worksheets with the same color tab.

 

If you’ve ever manually reorganized your worksheet tabs, you’ve probably wondered why there’s no built-in Excel feature that sorts worksheet tabs for you. And what about when you need to add a new worksheet? You need to click and drag to add it to your worksheet order. Thanks to a little bit of code, you can sort worksheet tabs automatically — in ascending or descending order — whether you’re sorting by name, date or color, as shown in Figure A.

 

Download: You can get all of the code from this article in a text file by downloading and extracting the file code.txt from the URL given at the beginning of this article

 

IME18805AIME18805a2IME18805a3

A:

These workbooks’ worksheet tabs were sorted automatically with our simple macros.

 

Start at the top

Let’s start with macros that will help you perform a general sort on your worksheet tabs. These macros will work for worksheet names that include text and numbers (not dates).

 

To build a macro that sorts worksheets in ascending order:

  1.  Launch Excel and open the workbook in which you want to sort worksheet tabs.
  2.  Press [Alt][F11] to open the Visual Basic Editor (VBE).
  3.  Enter the code in Listing A.

Listing A:

Code to sort worksheets by name in ascending order

Sub SortSheetsAsc()

Dim intCount As Integer

Dim intCounted As Integer

Dim intLastSheet As Integer

 

Application.ScreenUpdating = False

 

intLastSheet = Sheets.Count

For intCount = 1 To intLastSheet

For intCount2 = intCount To intLastSheet

If UCase(Sheets(intCount2).Name) < _

UCase(Sheets(intCount).Name) Then

Sheets(intCount2).Move _

Before:=Sheets(intCount)

End If

Next intCount2

Next intCount

 

Application.ScreenUpdating = True

End Sub

 

Learn the code structure

The macros we work with in this article all follow the same basic structure. Here’s a rundown of the code’s steps as it sorts your worksheets:

  1.  Disables screen updating so you don’t see your screen flicker as the code runs.
  2.  Sets the variable intLastSheet equal to the number of worksheets in the workbook.
  3.  Uses a For...Next loop that cycles through every worksheet in the workbook.
  4.  Starts a second For...Next loop that tests each worksheet name in the first loop cycle against each worksheet name in the second loop cycle. If the name comes earlier in the sort order (“less than”), Excel moves the worksheet before the first one.
  5.  Enables screen updating again.

When you nest one For...Next loop inside another, you can test for every combination of worksheet tabs. For instance, you’ll test the first loop’s worksheet against each of the worksheets in the second loop, then move to the first loop’s second worksheet and test that one against all of the second loop’s worksheets, and so on.

 

Reverse the sort order

You can easily create a second macro that performs the same function as our SortSheetsAsc() procedure — except that it sorts in descending order instead.

 

To create a second macro that sorts in descending order:

  1.  Copy the code in your VBE and paste it after the SortSheetsAsc() procedure so that the entire procedure appears twice.
  2.  Change the procedure name to SortSheetsDesc().
  3.  Replace the less than sign (<) with a greater than sign (>) in the If...End If statement.

Sort in chronological order

Sometimes it’s easier to name worksheets after dates, such as months o[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage