Sort your worksheet tabs automatically — even dates!

by Kara Soos

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

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

 

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