Hey, Scripting Guy! How can I sort worksheets in a workbook?

— FS

Hey, FS. How can you sort worksheets in a workbook? Well, to tell you the truth, not as easily as we had hoped you could sort worksheets in a workbook. This doesn’t mean it can’t be done; it just means that the process is a tiny bit convoluted. Because of that, don’t feel bad if you don’t fully understand everything we’re about to say; we’re not even sure if we fully understand it. But the script seems to work, and, ultimately, that’s all we care about anyway.

Let’s assume we have a workbook that has the following set of worksheets:

How do we sort these worksheets in alphabetical order? Here’s how:

On Error Resume Next

Dim arrNames()
intSize = 0
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Inventory.xls”)
For Each objWorksheet in objWorkbook.Sheets
    ReDim Preserve arrNames(intSize)
    arrNames(intSize) = objWorksheet.Name
    intSize = intSize + 1
Next
For i = (UBound(arrNames) – 1) to 0 Step -1
    For j= 0 to i
        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
            strHolder = arrNames(j+1)
            arrNames(j+1) = arrNames(j)
            arrNames(j) = strHolder
        End If
    Next
Next 
For i = UBound(arrNames) to 1 Step -1
    Set objSheet1 = objWorkbook.Sheets(arrNames(i))
    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
    objSheet2.Move objSheet1
Next

Yes, yes, we know. But we’re going to try to explain this as best we can.

Actually the script starts off simply enough. We begin by creating a dynamic array named arrNames(). Why? Well, although Excel has a ton of features built into it, it is missing at least one thing: a command for sorting worksheets. Because Excel has no built-in method for sorting worksheets we’re going to have to take care of that ourselves. And the way we’re going to do that is by grabbing all the worksheet names, storing those names in a dynamic array, and then sorting the items in that array. Once we’ve sorted the array we’ll know the correct order for all the worksheets; at that point we can then use Excel’s Move method to move each worksheet to the desired location.

So after we create the array we have some standard boilerplate code that creates an instance of the Excel.Application object, sets the Visible property to True, and then uses the Open method to open the file C:\Scripts\Inventory.xls. Now we’re ready to roll up our sleeves and start working.

The first thing we need to do is grab all the worksheet names and put them into our dynamic array. That’s what we do in this block of code:

For Each objWorksheet in objWorkbook.Sheets
    ReDim Preserve arrNames(intSize)
    arrNames(intSize) = objWorksheet.Name
    intSize = intSize + 1
Next

All we’re doing here is setting up a For Each loop to loop through the Sheets collection; as the name implies, this collection consists of all the worksheets found in our workbook. For each worksheet in the collection we use the ReDim Preserve command to resize our dynamic array. The array starts off as size 0, meaning it’s an array with a single item. (An array size will always be the number of items in the array minus 1.) How do we know that we set the size to 0? Because we assigned it the value of a counter variable named intSize, a variable that we set to 0 at the beginning of the script.

We then assign the first item in the array the name of the first worksheet in the collection; that’s what we do here:

arrNames(intSize) = objWorksheet.Name

We increment our counter variable by 1, then loop around and repeat the process for the second worksheet in the collection, a worksheet whose name will become the second item in our array. When we’re all done we’ll have an array consisting of the following items, in this order:

Sheet2
c
a
Sheet1
b

OK, that wasn’t too bad. That’s not necessarily true of this next section, however. Here we use a “simple” bubble sort to sort the items in the array in alphabetical order:

For i = (UBound(arrNames) – 1) to 0 Step -1
    For j= 0 to i
        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
            strHolder = arrNames(j+1)
            arrNames(j+1) = arrNames(j)
            arrNames(j) = strHolder
        End If
    Next
Next

We’re not going to try and explain the ins and outs of a bubble sort in today’s column; for a reasonably good explanation of how this works you might take a look at the Scripting Week 2 webcast Things the Scripting Guys Never Told You. In short, what happens in a bubble sort is that you compare each item in the array with every other item in the array, and then swap their places in that array if need be. For example, our first two items in the array are these:

Sheet2
c

Alphabetically-speaking, c comes before Sheet2. Therefore, our bubble sort will swap those two names, meaning that these become the first two items in the array:

c
Sheet2

This continues until each item has been compared with every other item. The net result? An array sorted in alphabetical order:

a
b
c
Sheet1
Sheet2

As luck would have it, this also turns out to be the order we want for our spreadsheets. Now that we know which spreadsheet should come first, which should come second, etc., we can use this block of code to rearrange the spreadsheets within the workbook:

For i = UBound(arrNames) to 1 Step -1
    Set objSheet1 = objWorkbook.Sheets(arrNames(i))
    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
    objSheet2.Move objSheet1
Next

What we’re doing here is starting at the end of the array (UBound connects us to the last item in the array, in this case Sheet2) and then working our way down (that’s what the Step -1 does). Thanks to our sorted array we know that the last item in the array – Sheet2 – should also be the last worksheet in the workbook. We also know that the next-to-last item in the array – Sheet1 – should be the next-to-last worksheet in the workbook. Therefore, we use this code to create an object reference to Sheet 2:

Set objSheet1 = objWorkbook.Sheets(arrNames(i))

We then use this code to create an object reference to Sheet1:

Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))

Finally, we call the Move method to move Sheet1 in front of (before) Sheet2:

objSheet2.Move objSheet1

How did we know that Sheet1 would be moved in front of Sheet2? That’s easy: there’s no comma after the Move method:

objSheet2.Move objSheet1

If we wanted to move Sheet1 in back of (after) Sheet2 then we would put a comma after the Move method:

objSheet2.Move, objSheet1

Admittedly, this isn’t the most intuitive thing in the world, but it works.

When all is said and done, we’ll end up with a workbook that looks like this:

And if you aren’t totally sure how we got to that point, well, don’t worry about it. Sometimes it’s enough just to have faith, without questioning the how or the why.