Unhide All Worksheets in a Workbook

If you want to unhide all Worksheets in a Workbook at once, this simple macro makes easy work for you.

VBA Code

Sub UnhideAllWorksheets()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Loop to next worksheet
    ws.Visible = xlSheetVisible
    Next ws
End Sub

How This Macro Works

  1. In Step 1 declares an object called ws. This creates a memory container for each worksheet the macro loops through.
  2. In Step 2, the macro starts the looping, telling Excel to enumerate through all worksheets in this workbook.
  3. In Step 3 changes the visible state to xlSheetVisible. Then it loops back to get the next worksheet.

How to Use This Macro

The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project window, it is named personal.xlsb.

  1. Activate the Visual Basic Editor by pressing ALT+F11.
  2. Right-click personal.xlsb in the Project window.
  3. Choose Insert ➜ Module.
  4. Type or paste the code in the newly created module.Insert a Module in Personal Macro Workbook

Don't see personal.xlsb in your project window?

If you don't see personal.xlsb in your project window, it doesn’t exist yet. You’ll have to record a macro, using Personal Macro Workbook as the destination.

To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down box.

Store Macro In Personal Macro Workbook

Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>