Sort Tabs Alphabetically in a Workbook
A while ago I was asked if it was possible in Excel to sort the worksheets alphabetically when there are well over 150 worksheet.
I just love when someone asks me if there is a command in Excel that could do something that would make their life so much simpler. Of course, I replied that Excel had no such feature. However, I was wondering if I could create that feature myself using VBA. So, I started to think how I could approach this challenge.
Download the Zip file containing a PDF document with the sample Excel workbook used in this post.
Thinking it through
I created a workbook with about 40 sheets using names of towns. If I can write in a worksheet all the sheet names then sort that list alphabetically, that would be a good start. The trick then would be to re-order (or move) the sheets using the newly ordered list.
Using an Array
Learning to use arrays in VBA is not that difficult. An array is very similar to a selection of multiple cells in Excel. You can make a one column selection or a one row selection. You can also make a two dimensional selection. You can do the same in VBA using arrays. An arrays is a variable that can hold as many elements as are required.
Let’s see a very simple example to play with. At the top of the module, I decide to add the line Option Base 1. This will simplify matching the array elements (which will now start with 1 instead of 0) with the sheet numbers (always start with 1). Then I tested a simple array of 5 towns and see how hard it would be to dump all the elements in a worksheet. The Sub below does just that:
Running this Sub (hit the F5 key) would type the five names is cells A1:A5 in the active sheet. This is the result of using the Excel built-in Transpose() function. Using A1:E1 instead would have written the towns horizontally.
In this sample code I manually specify the range A1:A5 because I knew in advance the size of the array. If a real situation we would need to find out before how many sheets are in the workbook then create an expression reflecting that range. For example if the number of sheets was stored in the variable x then we would use:
Range("A1:A" & x) ' If x = 20 then the range where we copy the sheet names would be A1:A20
Let’s apply what we learned with worksheets
Now that this part works well, let’s see how I can fill the array with an unknown number of sheets. Looping through each sheet to populate the array is not rocket science. The variable bytTotalSheets is used to store the total number of sheets. The array is then dimensioned to that size.
Arrays use index to store / retrieve elements, the variable i will be used as the index. So the element arySuburb(1) will contain the name of sheet 1, arySuburb(2) will contain the name of sheet 2, and so on. See the new Sub below.
Note that the 3 numeric variables are declared as Byte data type. This light data type runs extremely fast but it has a small drawback: it only holds number between 0 – 255. So if your workbook possibly has more than 255 sheet use the Integer datatype which can hold number up to 32,700 positive integers!
Now for the fun part.
We want to dump the values stored in all the elements of the array in a worksheet and sort that list. We will work with an invisible sheet (hidden). For this, we will need one last variable, a worksheet variable to refer to the newly created sheet. We will name it simply wks.
Add the following line in the variable declaration part at the beginning:
A range variable will be required to dump the sorted list of towns in a worksheet. In the screenshot above, a range variable is created with the name rng. We will use that one.
Sorting the list of worksheets
The lines below are quite self-explanatory. By default, Excel expects a list to have a column heading when sorting. We are not using one here hence the parameter header:=xlNo. The UBound() function used below stands for Upper Bound and returns the index of the last element. In other words, the total number of elements in the array.
Now that we have the sorted list of all the sheet names (excluding the hidden sheet created earlier), we can repopulate the array with what is now a sorted list. Once this is done we can safely delete that hidden sheet.
Notice the lines surrounding the wks.Delete instruction. The line above prevents Excel from asking the user to confirm the deletion of the worksheet. The line below reinstates the confirmation of deleting a worksheet. Obviously, it would be very disturbing for the user running some VBA procedure to be asked if it’s okay to delete a sheet!
Sorting the sheets is somehow tricky because they need to be re-ordered based of the updated array containing the sorted list of sheets.
After some thinking I found that the easiest way to do that would be to move the first sheet name in the sorted list before the first worksheet in the workbook. Then the second sheet name in the sorted list before the second worksheet. Third sheet name in the sorted list before the third, and so on.
If you’re a bit curious about how this works, consider the table below. The Unsorted column shows the original order. Imagine that you have the unsorted list in your hand and you want to move them in the proper order.
- In Move 1, Albany goes at the top and the other sheets are pushed down (Sheet 1 done)
- In Move 2, Bathurst moves in second place. Again, the rest of the sheets move down (Sheet 2 done)
- In Move 3, Dalmore goes in third position (Sheet 3 done)
- In Move 4, Newcastle goes in fourth position (Sheet 4 done)
And so on. In the code below is the portion of the code manipulating the sheets as described above. Here I used the variable j but we could have any name really as long as it was declared at the top.
While the sheets are ordered, the screen is frozen (by turning off the ScreenUpdating property) so that the task is done hundred times faster. Normally you do this once the project is complete. At the very end, it is good practice to turn ScreenUpdating back on.
I tested this procedure in a workbook containing over 200 sheets (by holding Shift+F11 for about 10 seconds). It sorted the worksheets in just under 1.0 seconds!
Hope this was useful.
Daniel from ComboProjects