It’s often the case that workbooks get very large and end up with a lot of sheets. Excel doesn’t have a lot of built in functionality to make working with a large number of sheets easy.
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!
Sub SortSheets() Dim SheetCount As Integer Application.ScreenUpdating = False SheetCount = Worksheets.Count If SheetCount = 1 Then Exit Sub For i = 1 To SheetCount - 1 For j = i To SheetCount - 1 If Worksheets(j + 1).Name < Worksheets(i).Name Then Worksheets(j + 1).Move Before:=Worksheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
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:
If Worksheets(j + 1).Name < Worksheets(i).Name Then
And change it to this line of code:
If Worksheets(j + 1).Tab.Color < Worksheets(i).Tab.Color Then
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.
Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = True Next ws End Sub
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.
Sub HideSheetsInTable() Dim SheetNames As Range Dim SheetName As Range Set SheetNames = Sheets("Hide Sheets").ListObjects("HiddenSheets").ListColumns("Sheet Name").DataBodyRange On Error Resume Next For Each SheetName In SheetNames.Cells Worksheets(SheetName.Value).Visible = False Next SheetName End Sub
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.
Sub ListAllSheets() Dim ws As Worksheet Dim Counter As Integer Counter = 0 For Each ws In ActiveWorkbook.Worksheets Sheets("Index").Range("A1").Offset(Counter, 0).Value = ws.Name Counter = Counter + 1 Next ws End Sub