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