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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

Download the Excel file that contains the VBA macro code.

Hide Unhide Multiple Sheets Macro.xlsm (16.6 KB)

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.

Hiding and Unhiding Multiple Sheets in Excel is a Pain

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.

Macro to Hide and Unhide All Sheets with Same 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.

Store Hide and Unhide Sheets Macros in Personal Macro Workbook

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

Add Hide Unhide Macro Buttons to 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.

Excel Color Menu Standard Colors

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.

ColorValue
Dark Red192
Red255
Orange49407
Yellow65535
Light Green5296274
Green5287936
Light Blue15773696
Blue12611584
Dark Blue6299648
Purple10498160

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.

Select Multiple Sheets with Ctrl or Shift Then Change Tab Colors

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.

Quickly Hide and Unhide Multiple Sheets with Tab Control Add-in

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! 🙂

3 comments

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

  • Thank you for everything you do Jon! I learned a lot from your videos! Question: I’ve recently updated to Office 2019 and I’m finding a lot of the files containing macros created in Excel 2010 are getting corrupted (no crazy macros, mainly scraping from the web & posting data to other workbooks etc). Do you have any experience with this & can you give me any tips? Should I export my macros & recreate the files from scratch in 2019? I’m at a complete loss….

    Thank you

  • Hello,

    I am quite a beginner in VBA but that’s definitively something that could ease my life.
    Could you also give the macros for the other solutions (hidding sheets ending with a specific letter and according to the table)?

    thanks a lot!

    • Hi Laure,
      Awesome! I’m happy to hear you’ll be making use of this. I just added the code for the hiding sheets based on the tab name ending in a specific letter, and a cell containing a specific value.

      I updated the sample file with this code and put it in the article above.

      The code to create the sheet is a bit more in-depth, as you have an additional element that requires updating when sheet names change. However, I’ll post a solution for this in the future.

      The Tab Control add-in has the list solution built into it as well.

      I hope that helps. Thanks again and have a nice day! 🙂

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar