How to Unhide and Rehide Multiple Sheets in Excel

Bottom line: Learn how to use a set of macros to automate the process of unhiding and re-hiding multiple worksheets (tabs).

Skill level: Intermediate

Video Tutorial

Download the Excel File

Download the Excel file that contains the VBA macro code.

Unhiding Sheets is a Pain

Do you have any workbooks that you have to hide a bunch of sheets before sending them to other users or co-workers?

Maybe you need to unhide the sheets to update data, modify formulas, or tie out numbers. Then rehide those sheets before distributing the file.

This process can be time consuming if it's something you do every week or month. Excel doesn't even allow us to unhide multiple sheets at the same time. So, I created a set of macros to automate the task.

Hide & Unhide by Tab Color

There are many ways to solve this problem. The macros I wrote are a very simple solution. They hide and unhide sheets based on their tab color.

In this example the macros will hide and unhide all the yellow colored tabs.

How the Macros Work

The macros loop through all sheets in the workbook and hides or unhides any sheet with a yellow tab color. You can change this to any other color.

Macro to Hide Colored Sheets

The first macro hides the sheets based on their tab color. Here is the VBA code to Hide Yellow Sheets:

'Set tab color to hide & unhide
Const TABCOLOR As Long = 65535 'Yellow


Sub Hide_Yellow_Sheets()
'Hide all sheets with yellow colored tab

Dim ws As Object 'Use object instead of Worksheet for Chart Sheets

  'Loop through sheets and hide yellow tabs
  For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = TABCOLOR Then
      ws.Visible = xlSheetHidden
    End If
  Next ws

End Sub

Note: The macros can also be modified to make the sheets VeryHidden. Set the Visible property to xlVeryHidden in the macro above.

Macro to Unhide Colored Sheets

The second macro unhides sheets with the same yellow tab color. Here is the VBA code to Unhide Yellow Sheets:

Sub Unhide_Yellow_Sheets()
'Unhide all sheets with yellow colored tab

Dim ws As Object 'Use object instead of Worksheet for Chart Sheets

  Application.ScreenUpdating = False
  
  'Loop through sheets and unhide yellow tabs
  For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = TABCOLOR Then
      ws.Visible = xlSheetVisible
    End If
  Next ws

  Application.ScreenUpdating = True

End Sub

How to Use the Macros

I recommend storing these macros in your Personal Macro Workbook. That way you can run the macros on any open workbook on your computer.

In the video above, I show how I also create macro ribbon buttons and add them to a custom ribbon tab.

This makes it super easy to run the macros. Simply change the tab color to the “hide” color, then run either of the macros. Your workbook will instantly be ready for you to either work on (unhidden sheets) or distribute to users (hidden sheets).

Use Standard Colors

You can use any color you'd like for these macros. You will just need to change the value that the TABCOLOR constant is set to at the top of the code module. The TABCOLOR constant (variable) is used in both the hide and unhide macros. So you only have to change it in one place.

I recommend using one of the Standard Colors because these colors do not change with the Theme. It will be easier for you and other users to apply a tab color from the Standard Colors palette.

Here is a list of the Standard Color Values for the Color Property.

Color	Value
Dark Red	192
Red	255
Orange	49407
Yellow	65535
Light Green	5296274
Green	5287936
Light Blue	15773696
Blue	12611584
Dark Blue	6299648
Purple	10498160

Change Multiple Sheet Colors

As I mention in the video, you can select multiple sheets by holding the Ctrl or Shift keys. Then right-click one of the selected sheets and select a color from the Tab Color sub-menu.

Other Solutions

There are A LOT of different ways to go about this process.

Naming Convention

You can flag the sheets to be hidden with a naming convention. For example you might put a “-h” at the end of all sheet names that should be hidden. Then modify the macro to for that criteria.

Here is an example of the VBA code:

'Set tab naming convention to hide & unhide
Const TABNAME As String = "-h"


Sub Hide_Named_Sheets()
'Hide all sheets that end with -h

Dim ws As Object 'Use object instead of worksheet for Chartsheets

  'Hide sheets with sheet name ending in -h
  For Each ws In ActiveWorkbook.Sheets
    If Right(ws.Name, 2) = TABNAME Then
      ws.Visible = xlSheetHidden
    End If
  Next ws

End Sub


Sub Unhide_Named_Sheets()
'Unhide all sheets that end with -h

Dim ws As Object 'Use object instead of worksheet for Chartsheets

  'Unhide sheets with sheet name ending in -h
  For Each ws In ActiveWorkbook.Sheets
    If Right(ws.Name, 2) = TABNAME Then
      ws.Visible = xlSheetVisible
    End If
  Next ws

End Sub

Value in Specific Cell

Another option is to put a value in a specific cell on each sheet that needs to be hidden. Then modify the macro to check the value of that cell on each sheet. This solution won't work with chart sheets.

'Set tab naming convention to hide & unhide
Const CELLVALUE As String = "Hide"


Sub Hide_Named_Sheets()
'Hide all sheets that contain a value in a specific cell

Dim ws As Worksheet

  'Hide sheets with value of Hide in cell A2
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("A2").Value = CELLVALUE Then
      ws.Visible = xlSheetHidden
    End If
  Next ws

End Sub


Sub Unhide_Named_Sheets()
'Unide all sheets that contain a value in a specific cell

Dim ws As Worksheet

  'Unide sheets with value of Hide in cell A2
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("A2").Value = CELLVALUE Then
      ws.Visible = xlSheetVisible
    End If
  Next ws

End Sub

The Tab Control Add-in

A more advanced option is to create a sheet with a list of the sheet names to be hidden, then have the macro loop through the list on that sheet. This is exactly what my Tab Control Add-in does, which I show at the end of the video above.

Tab Control contains additional features that allow you to rename sheets, change tab colors, and even make the hiding dynamic based on formulas or other conditions.

Tab Control comes with our Tab Hound Add-in, which is packed with additional features that make it easy to navigate and work with the sheets in your workbook.

Conclusion

I hope this set of macros helps save you time with hiding and unhiding the same set of sheets in a workbook.

There are many ways to go about this. I like the tab color option because it is very easy to implement. You simply change the tab colors of the sheets, then run the macros. This makes it easy to add or remove sheets from the group.

Do you have other ways to go about this process? Please leave a comment below with any suggestions or feedback. Thanks so much! 🙂