In this article, we will go through the code that helps us work on all worksheets of a workbook.

There may be a need to do it when some sheets need to be formatted or some data needs to be inserted in the sheets. This could happen on opening the workbook or at the time of some specific calculation change in the data stored in the cells.

Count the Worksheets in a Workbook

Here is the code that can count the number of worksheets in a workbook. The syntax indicates the number of worksheets in the active workbook (open and worked on).

Syntax:

ActiveWorkbook.Worksheets.Count

A Sample Program to Display the Names of All the Worksheets in a Workbook

The code below prints the names of all the worksheets in the “activeworkbook” document in the immediate window. First, two variables are declared, one to hold the number of worksheets, and the other acts as the iterator while moving through the sheets. Then a value is assigned to the count WS_nos (count) variable. Once this is done, a loop is set up (in this program, it is a “for” loop) that iterates through each sheet in the workbook. We print the sheet’s name inside the loop using a message box.

Sub worksheets_demo()


         Dim WS_nos As Integer
         Dim sheet_num As Integer

         ' Set WS_nos equal to the number of worksheets in the workbook that is active
         WS_nos = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For sheet_num = 1 To WS_nos

            ' We are trying to display the name of the referenced worksheet here.
            
            Debug.Print ActiveWorkbook.Worksheets(sheet_num).name

         Next sheet_num

      End Sub

Sample Program to Hide Any Specific Sheet

In this program, we get a sheet name from the end user as input and try to match it with the list of sheet names in the workbook. If found, the sheet is hidden, and a message is displayed. If not found, another message stating that the sheet wasn’t found is displayed. The input value and the sheets which we iterate through are wrapped in an uppercase function so that we do not miss the matching sheet. i.e., both the sheet names are converted to uppercase and compared.

This method of hiding a sheet does not allow the end user to unhide the sheet without the code.

Sub worksheets_demo_hide()
        ' declaration of variables

         Dim WS_nos As Integer
         Dim sheet_name As String
         Dim flag As Boolean
         
         ' set an initial value for the flag
         flag = False
         ' get the name of the sheet from the user
            sheet_name = InputBox("Enter the name of the sheet you want to hide ")
         ' Begin the loop.
         For Each cur_sheetname In ActiveWorkbook.Worksheets

            ' see if the sheetname matches with the current sheet.
            If UCase(sheet_name) = UCase(cur_sheetname.name) Then
                ' hide the sheet
                ActiveWorkbook.Worksheets(cur_sheetname.name).Visible = xlSheetVeryHidden
                ' set the flag value to true as we have got the sheet and disabled it.
                flag = True
            End If
         Next

         If flag = False Then ' if the sheet is not found
         MsgBox "The sheet " & sheet_name & " is not found."
         Else ' if the sheet was found and hidden
         MsgBox "The sheet " & sheet_name & " has been hidden successfully."
         End If
End Sub

Output:

If the sheet input by the end user exists in the document, it is hidden, and a relevant message is displayed. If it is not found, the other message is displayed:

Sample Program to Unhide Any Specific Sheet

A specific sheet can also be unhidden using VBA. In the same example, instead of the keyword “xlSheetVeryHidden,” we can use “True” as the value to unhide the hidden sheet (make it visible). This does not lead to any error even if the sheet is already unhidden/visible.

Sub worksheets_demo_unhide()

         Dim WS_nos As Integer
         Dim sheet_name As String
         Dim flag As Boolean
         
         ' set an initial value for the flag
         flag = False
         ' get the name of the sheet from the user
            sheet_name = InputBox("Enter the name of the sheet you want to hide ")
         ' Begin the loop.
         For Each cur_sheetname In ActiveWorkbook.Worksheets

            ' see if the sheetname matches with the current sheet.
            If UCase(sheet_name) = UCase(cur_sheetname.name) Then
                ' Unhide the sheet
                ActiveWorkbook.Worksheets(cur_sheetname.name).Visible = True
                ' set the flag to true as the sheet has been found and it is made visible
                flag = True
            End If
         Next

         If flag = False Then
         ' message that the sheet is not found
         MsgBox "The sheet " & sheet_name & " is not found."
         Else
         ' message that the sheet is made visible
         MsgBox "The sheet " & sheet_name & " is unhidden successfully."
         End If
End Sub

Output:

Whether is the sheet with the name entered in the Input box by end user is visible/hidden, the code makes it visible. If the sheet is not found/does not exist in the workbook, the other message stating that the sheet is not found is displayed.

Summary

Apart from what has been demonstrated above, we can also delete the format of sheets/cells in some sheets based on specific criteria. Updating sheets with some content is also possible.

Though macro changes are irreversible once run, as I always say, hiding and unhiding can be done alternatively to undo the action (LOL 😊).

Other programs like coloring tabs, insertion of comments, report creation, and data setup on load of the document in worksheets can be explored using this feature of worksheet access in Macros.