[Excel VBA] Macro to Unhide All Hidden Sheets at Once

Introduction

Do you often work with Excel workbooks that contain many hidden sheets? Right-clicking and selecting “Unhide” for each sheet one by one is a tedious task, especially if you have dozens of sheets to reveal.

You can solve this problem instantly with a VBA macro. In this article, I will introduce a short and very useful code to unhide all sheets in a workbook at once.

VBA Sample Code to Unhide All Sheets

The logic of this macro is very simple: “Look at every sheet in the workbook one by one and set its visibility to True.”

' Unhide all hidden sheets in the workbook
Sub UnhideAllWorksheets()

    '== Define variable ==
    Dim targetSheet As Worksheet

    '== Loop through all sheets in the workbook ==
    For Each targetSheet In ThisWorkbook.Worksheets
        ' Set the sheet visibility to Visible (True)
        targetSheet.Visible = True
    Next targetSheet
    
    MsgBox "All sheets have been unhidden.", vbInformation

End Sub

How to Use

1. Open the VBE (Visual Basic Editor) by pressing Alt + F11.
2. Paste the code above into a Standard Module.
3. Run the UnhideAllWorksheets macro.

Just by doing this, all hidden sheets will appear immediately.

Code Explanation

• Dim targetSheet As Worksheet This declares a variable of the Worksheet type. This variable, targetSheet, will hold each sheet temporarily during the loop process.
• For Each targetSheet In ThisWorkbook.Worksheets This is the core of the code. Using the For Each ... Next syntax, the macro retrieves sheets one by one from the ThisWorkbook.Worksheets collection.
• targetSheet.Visible = True This sets the Visible property, which controls the sheet’s display state, to True. This single line makes hidden sheets visible. If a sheet is already visible, it remains visible, so there is no error.

Summary

In this article, I introduced a simple yet practical macro to unhide all hidden sheets at once.

With just a few lines of code, you can free yourself from repetitive manual work. If you save this macro in your Personal Macro Workbook, you can use it with any Excel file, further improving your efficiency. Please add this useful code to your VBA toolbox.