To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

1. Right click on a the Worksheets tab
2. Click the Unhide button in the opened tab
3. Select the Worksheet you want to unhide and click Ok

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Sheets("NameOfWorksheet").Visible = xlSheetVisible

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

Sheets("NameOfWorksheet").Visible = xlSheetHidden

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

For Each ws In Sheets:ws.Visible=xlSheetVisible:Next ws

Below the same code as above but spread across multiple lines. Let us run through this:

'Will loop through all worsheets in the ActiveWorkbook
For Each ws In Sheets
  'Sets the visibility of the Worksheet to true
  ws.Visible=xlSheetVisible
Next ws

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

'Loop all worksheets in the Excel file
For Each ws In Sheets
  'If the name of the Worksheet starts with "Hidden" then...
  If ws.Name Like "Hidden*" Then
     '...make the worksheet visible
     ws.Visible = xlSheetVisible
  End If
Next ws

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

For Each ws In Sheets: ws.Visible=Iif(ws.Visible = xlSheetVisible or ws.Name Like "Hidden*",ws.Visible,xlSheetHidden):Next ws

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

'Runs when the form is created. Lists all worksheets and selects visible ones
Private Sub UserForm_Initialize()
    Dim it As Long
    lSheets.MultiSelect = fmMultiSelectExtended 'Make sure more than 1 can Sheet can be selected
    For Each ws In ActiveWorkbook.Sheets
        lSheets.AddItem ws.Name
        lSheets.Selected(it) = IIf(ActiveWorkbook.Sheets(ws.Name).Visible = xlSheetVisible, True, False)
        it = it + 1
    Next ws
End Sub

'Runs when button is clicked
Private Sub cbRun_Click()
    'First make visible selected Worksheets as otherwise code might crash
    Dim i As Long
    For i = 0 To lSheets.ListCount - 1
        If lSheets.Selected(i) = True Then ActiveWorkbook.Sheets(lSheets.List(i)).Visible = xlSheetVisible
    Next i

    'Hide unselected Worksheets
    For i = 0 To lSheets.ListCount - 1
        If lSheets.Selected(i) = False Then ActiveWorkbook.Sheets(lSheets.List(i)).Visible = xlSheetHidden
    Next i
End Sub

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

Sub ShowWorksheets()
    Dim mw As ManageWorksheets
    Set mw = New ManageWorksheets
    mw.Show
End Sub

When executed this is how it will look like: