Excel is not just a tool for keeping company data and accounting. Its power lies in the fact that it is used to classify and manage almost anything in a simple and fast way. We can create agendas, work quadrants, address books, invoice templates, attendance records ... However, Excel does not allow us to organize workbook sheets, something very useful when working with long books. But we can sort the Excel tabs automatically with a macro in Visual Basic code . We explain how to do it step by step.
The words "macro in Visual Basic code" can be intimidating, but everything is easier than it sounds. A macro is a small program that executes a series of actions for us in Excel, and serves to automate repetitive tasks . Macros are written in Visual Basic for Applications (or VBA) code, a programming language that allows you to access and extend the functions of Excel.
The Visual Basic editor
To create our macro, we have to open any workbook in Excel, and the more tabs it has, the better.
To open the VBA editor, we use the keyboard shortcut Alt + F11 . Once the editor window appears, we look for a particular Excel workbook, called Personal.xls . This book has the property that whatever we do in it will affect all the workbooks . That is, if we create a macro, we can use it whenever we want.
If you can't find the Personal book, don't worry. Exit the editor, open the Excel View tab , pull down the Macros menu , and select Record Macro . Save the macro in your Personal Macro Book and start recording. Next, bring up the Macros menu again and stop recording. You already have your "special" book.
Now we have everything ready to create a new module . We open the VBA editor with Alt + F11 , select the Personal.xls book, and then click Insert> Module . We place the cursor in the module window and write the following code:
Sub SortSheets_Ascendant ()
For a = 1 To Sheets.Count
For s = a + 1 To Sheets.Count
If UCase (Sheets (a) .Name)> UCase (Sheets (s) .Name) Then
Sheets (s) .Move Before: = Sheets (a)
End If
Next s
Next to
End Sub
We close the window, and the macro will have been stored automatically. It only remains to check that it works. We open the book whose tabs we want to sort, we go to View and click on Macros , or we press the keyboard shortcut Alt + F8 . Double click on SortSheets_Ascendant , and the book tabs will be placed in alphabetical order in an instant .
When we close Excel, the program will ask us if we want to save the changes made to the macros. We accept, and our macro will be stored for use with the book we want.
Sort the sheets in descending order
With a few small code changes, we can achieve the reverse effect: sort the tabs in descending alphabetical order . We repeat the process for creating the macro, and this time we insert the following code:
Sub SortSheets_Descending ()
For a = 1 To Sheets.Count
For s = a + 1 To Sheets.Count
If UCase (Sheets (a) .Name) <UCase (Sheets (s) .Name) Then
Sheets (s) .Move Before: = Sheets (a)
End If
Next s
Next to
End Sub
There are only two variations. The first, of course, is the name of the macro. The second is the change from> to <in the fourth line of code. This means that the ordering of the sheets in the book will change direction . Now when you click View> Macros (or Alt + F8 shortcut ), you will see the two newly created macros. Double click on SortSheets_Descending , and check with satisfaction its effect.
Saving the changes when exiting Excel, we will have these two macros available at any time . They will save us a lot of time and effort when handling tabs of Excel workbooks that are especially bulky.