{"id":68473,"date":"2005-11-22T20:59:00","date_gmt":"2005-11-22T20:59:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/11\/22\/how-can-i-sort-worksheets-in-a-workbook\/"},"modified":"2005-11-22T20:59:00","modified_gmt":"2005-11-22T20:59:00","slug":"how-can-i-sort-worksheets-in-a-workbook","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-sort-worksheets-in-a-workbook\/","title":{"rendered":"How Can I Sort Worksheets in a Workbook?"},"content":{"rendered":"

\"Hey, \n

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

— FS<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

Let\u2019s assume we have a workbook that has the following set of worksheets:<\/P>\"Microsoft \n


How do we sort these worksheets in alphabetical order? Here\u2019s how:<\/P>

On Error Resume Next<\/p>\n
Dim arrNames()\nintSize = 0<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Inventory.xls”)<\/p>\n
For Each objWorksheet in objWorkbook.Sheets\n    ReDim Preserve arrNames(intSize)\n    arrNames(intSize) = objWorksheet.Name\n    intSize = intSize + 1\nNext<\/p>\n
For i = (UBound(arrNames) – 1) to 0 Step -1\n    For j= 0 to i\n        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then\n            strHolder = arrNames(j+1)\n            arrNames(j+1) = arrNames(j)\n            arrNames(j) = strHolder\n        End If\n    Next\nNext <\/p>\n
For i = UBound(arrNames) to 1 Step -1\n    Set objSheet1 = objWorkbook.Sheets(arrNames(i))\n    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))\n    objSheet2.Move objSheet1\nNext\n<\/PRE>\n
Yes, yes, we know. But we\u2019re going to try to explain this as best we can.<\/P>\n
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\u2019re going to have to take care of that ourselves. And the way we\u2019re 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\u2019ve sorted the array we\u2019ll know the correct order for all the worksheets; at that point we can then use Excel\u2019s Move<\/B> method to move each worksheet to the desired location.<\/P>\n
So after we create the array we have some standard boilerplate code that creates an instance of the Excel.Application<\/B> object, sets the Visible<\/B> property to True, and then uses the Open<\/B> method to open the file C:\\Scripts\\Inventory.xls. Now we\u2019re ready to roll up our sleeves and start working.<\/P>\n
The first thing we need to do is grab all the worksheet names and put them into our dynamic array. That\u2019s what we do in this block of code:<\/P>
For Each objWorksheet in objWorkbook.Sheets\n    ReDim Preserve arrNames(intSize)\n    arrNames(intSize) = objWorksheet.Name\n    intSize = intSize + 1\nNext\n<\/PRE>\n
All we\u2019re doing here is setting up a For Each loop to loop through the Sheets<\/B> 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<\/B> command to resize our dynamic array. The array starts off as size 0, meaning it\u2019s 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.<\/P>\n
We then assign the first item in the array the name of the first worksheet in the collection; that\u2019s what we do here:<\/P>
arrNames(intSize) = objWorksheet.Name\n<\/PRE>\n
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\u2019re all done we\u2019ll have an array consisting of the following items, in this order:<\/P>
Sheet2\nc\na\nSheet1\nb\n<\/PRE>\n
OK, that wasn\u2019t too<\/I> bad. That\u2019s not necessarily true of this next section, however. Here we use a \u201csimple\u201d bubble sort to sort the items in the array in alphabetical order:<\/P>
For i = (UBound(arrNames) – 1) to 0 Step -1\n    For j= 0 to i\n        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then\n            strHolder = arrNames(j+1)\n            arrNames(j+1) = arrNames(j)\n            arrNames(j) = strHolder\n        End If\n    Next\nNext\n<\/PRE>\n
We\u2019re not going to try and explain the ins and outs of a bubble sort in today\u2019s 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<\/B><\/A>. 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:<\/P>
Sheet2\nc\n<\/PRE>\n
Alphabetically-speaking, c comes before Sheet2. Therefore, our bubble sort will swap those two names, meaning that these<\/I> become the first two items in the array:<\/P>
c\nSheet2\n<\/PRE>\n
This continues until each item has been compared with every other item. The net result? An array sorted in alphabetical order:<\/P>
a\nb\nc\nSheet1\nSheet2\n<\/PRE>\n
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:<\/P>
For i = UBound(arrNames) to 1 Step -1\n    Set objSheet1 = objWorkbook.Sheets(arrNames(i))\n    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))\n    objSheet2.Move objSheet1\nNext\n<\/PRE>\n
What we\u2019re doing here is starting at the end of the array (UBound<\/B> connects us to the last item in the array, in this case Sheet2) and then working our way down (that\u2019s what the Step -1<\/B> 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:<\/P>
Set objSheet1 = objWorkbook.Sheets(arrNames(i))\n<\/PRE>\n
We then use this code to create an object reference to Sheet1:<\/P>
Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))\n<\/PRE>\n
Finally, we call the Move<\/B> method to move Sheet1 in front of (before) Sheet2:<\/P>
objSheet2.Move objSheet1\n<\/PRE>\n
How did we know that Sheet1 would be moved in front of Sheet2? That\u2019s easy: there\u2019s no comma after the Move method:<\/P>
objSheet2.Move objSheet1\n<\/PRE>\n
If we wanted to move Sheet1 in back of (after) Sheet2 then we would<\/I> put a comma after the Move method:<\/P>
objSheet2.Move, objSheet1\n<\/PRE>\n
Admittedly, this isn\u2019t the most intuitive thing in the world, but it works.<\/P>\n
When all is said and done, we\u2019ll end up with a workbook that looks like this:<\/P>\"Microsoft \n

And if you aren\u2019t totally sure how we got to that point, well, don\u2019t worry about it. Sometimes it\u2019s enough just to have faith, without questioning the how or the why.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t mean it can\u2019t be done; it just means that the process […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-68473","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019t mean it can\u2019t be done; it just means that the process […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68473","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=68473"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68473\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=68473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}