Simple tasks like listing out all sheets, sorting sheets alphabetically, sorting sheets by colour, hiding and unhiding sheets are all commonly done tasks in a workbook. Unforntunately, the options in Excel to do this are mostly manual. These are some common sheet tasks that we can automate with a bit of VBA code!
Sorting Sheets Alphabetically
You can easily reorder your sheets by drag and drop or you can right click and use the Move or Copy menu. This is ok if you only want to reorder one or two sheets. If you have a lot of sheets and you want to do something more complex like order them alphabetically, then you might get frustrated because there’s no option in Excel to order sheets. This VBA code will sort your sheets in ascending alphabetical order! This code can be adjusted slightly to order sheets in descending alphabetical order by changing the < symbol to a > symbol.
Sorting Sheets by Colour
People often colour code their tabs. We can adjust our sorting code a bit more to group sheets by colour. We just need to make a slight adjustment to this line of code: And change it to this line of code: This will allow
Hiding and Unhiding Sheets
It’s easy to hide multiple sheets at a time. All you need to do is select all the sheets you want to hide and then right click on them and select Hide from the menu. You can select multiple sheets by selecting the first sheet then holding Shift and selecting the last sheet. This will select all sheets between the two sheets. You can also select multiple non-adjacent sheets by holding the Ctrl key while selecting your sheets.
Unfortunately, it’s not as easy to unhide multiple sheets. When you right click in the sheet tab area and select Unhide in the menu, it will bring up a dialog box that shows all the hidden sheets in the workbook. This dialog box does not allow for multiple selection 🙁 If you want to unhide all your hidden sheets, you will need to repeat the process for each sheet. This is where a bit of VBA a can save quite a lot of time! This procedure will cycle through all the sheets in the workbook and set them to be visible.
Hiding Sheets Based on a List
While you may want to unhide all sheets, it’s likely that you don’t want to hide all the sheets in a workbook. You may just have a small number of sheets in a workbook that you don’t want the user to see. You want to be able to unhide all sheets so you can update the workbook then hide only a selection of the sheets when done.
One solution could be to maintain a list of sheet names which you want to hide. We can then loop through this list and set the sheet to be hidden. This code refers to a table named HiddenSheets in a sheet called Hide Sheets. It loops through the values in the table and hides any sheets with those names. If it comes across a name that’s not a sheet, it will skip over it and continue.
Listing All Sheets in a Workbook
We can also list all the sheets in a workbook. In this code we loop through all the sheets and list them in a sheet called Index starting in cell A1 and moving down the rows.
