The way to Unhide and Rehide A number of Sheets in Excel

Backside line: Learn to use a set of macros to automate the method of unhiding and re-hiding a number of worksheets (tabs).

Ability degree: Intermediate

Video Tutorial

Obtain the Excel File

Obtain the Excel file that accommodates the VBA macro code.

Disguise Unhide A number of Sheets Macro.xlsm (16.6 KB)

Unhiding Sheets is a Ache

Do you will have any workbooks that it’s a must to disguise a bunch of sheets earlier than sending them to different customers or co-workers?

Perhaps it’s essential unhide the sheets to replace knowledge, modify formulation, or tie out numbers. Then rehide these sheets earlier than distributing the file.

This course of could be time consuming if it is one thing you do each week or month. Excel would not even enable us to unhide a number of sheets on the similar time. So, I created a set of macros to automate the duty.

Disguise & Unhide by Tab Colour

There are lots of methods to resolve this drawback. The macros I wrote are a quite simple answer. They disguise and unhide sheets primarily based on their tab coloration.

On this instance the macros will disguise and unhide all of the yellow coloured tabs.

How the Macros Work

The macros loop by means of all sheets within the workbook and hides or unhides any sheet with a yellow tab coloration. You may change this to another coloration.

Macro to Disguise Coloured Sheets

The primary macro hides the sheets primarily based on their tab coloration. Right here is the VBA code to Disguise Yellow Sheets:

‘Set tab coloration to cover & unhide
Const TABCOLOR As Lengthy = 65535 ‘Yellow

Sub Hide_Yellow_Sheets()
‘Disguise all sheets with yellow coloured tab

Dim ws As Object ‘Use object as an alternative of Worksheet for Chart Sheets

  ‘Loop by means of sheets and conceal yellow tabs
  For Every ws In ActiveWorkbook.Sheets
    If ws.Tab.Colour = TABCOLOR Then
      ws.Seen = xlSheetHidden
    Finish If
  Subsequent ws

Finish Sub

Notice: The macros can be modified to make the sheets VeryHidden. Set the Seen property to xlVeryHidden within the macro above.

Macro to Unhide Coloured Sheets

The second macro unhides sheets with the identical yellow tab coloration. Right here is the VBA code to Unhide Yellow Sheets:

Sub Unhide_Yellow_Sheets()
‘Unhide all sheets with yellow coloured tab

Dim ws As Object ‘Use object as an alternative of Worksheet for Chart Sheets

  Software.ScreenUpdating = False
  
  ‘Loop by means of sheets and unhide yellow tabs
  For Every ws In ActiveWorkbook.Sheets
    If ws.Tab.Colour = TABCOLOR Then
      ws.Seen = xlSheetVisible
    Finish If
  Subsequent ws

  Software.ScreenUpdating = True

Finish Sub

The way to Use the Macros

I like to recommend storing these macros in your Private Macro Workbook. That method you’ll be able to run the macros on any open workbook in your pc.

Within the video above, I present how I additionally create macro ribbon buttons and add them to a customized ribbon tab.

This makes it tremendous straightforward to run the macros. Merely change the tab coloration to the “disguise” coloration, then run both of the macros. Your workbook will immediately be prepared so that you can both work on (unhidden sheets) or distribute to customers (hidden sheets).

Use Normal Colours

You should use any coloration you want for these macros. You’ll simply want to alter the worth that the TABCOLOR fixed is ready to on the prime of the code module. The TABCOLOR fixed (variable) is utilized in each the disguise and unhide macros. So that you solely have to alter it in a single place.

I like to recommend utilizing one of many Normal Colours as a result of these colours don’t change with the Theme. It is going to be simpler for you and different customers to use a tab coloration from the Normal Colours palette.

Here’s a checklist of the Normal Colour Values for the Colour Property.

ColourWorthDarkish Pink192Pink255Orange49407Yellow65535Gentle Inexperienced5296274Inexperienced5287936Gentle Blue15773696Blue12611584Darkish Blue6299648Purple10498160

Change A number of Sheet Colours

As I point out within the video, you’ll be able to choose a number of sheets by holding the Ctrl or Shift keys. Then right-click one of many chosen sheets and choose a coloration from the Tab Colour sub-menu.

Different Options

There are A LOT of various methods to go about this course of.

Naming Conference

You may flag the sheets to be hidden with a naming conference. For instance you may put a “-h” on the finish of all sheet names that ought to be hidden. Then modify the macro to for that standards.

Right here is an instance of the VBA code:

‘Set tab naming conference to cover & unhide
Const TABNAME As String = “-h”

Sub Hide_Named_Sheets()
‘Disguise all sheets that finish with -h

Dim ws As Object ‘Use object as an alternative of worksheet for Chartsheets

  ‘Disguise sheets with sheet title ending in -h
  For Every ws In ActiveWorkbook.Sheets
    If Proper(ws.Identify, 2) = TABNAME Then
      ws.Seen = xlSheetHidden
    Finish If
  Subsequent ws

Finish Sub

Sub Unhide_Named_Sheets()
‘Unhide all sheets that finish with -h

Dim ws As Object ‘Use object as an alternative of worksheet for Chartsheets

  ‘Unhide sheets with sheet title ending in -h
  For Every ws In ActiveWorkbook.Sheets
    If Proper(ws.Identify, 2) = TABNAME Then
      ws.Seen = xlSheetVisible
    Finish If
  Subsequent ws

Finish Sub

Worth in Particular Cell

Another choice is to place a worth in a particular cell on every sheet that must be hidden. Then modify the macro to examine the worth of that cell on every sheet. This answer will not work with chart sheets.

‘Set tab naming conference to cover & unhide
Const CELLVALUE As String = “Disguise”

Sub Hide_Named_Sheets()
‘Disguise all sheets that comprise a price in a particular cell

Dim ws As Worksheet

  ‘Disguise sheets with worth of Disguise in cell A2
  For Every ws In ActiveWorkbook.Worksheets
    If ws.Vary(“A2”).Worth = CELLVALUE Then
      ws.Seen = xlSheetHidden
    Finish If
  Subsequent ws

Finish Sub

Sub Unhide_Named_Sheets()
‘Unide all sheets that comprise a price in a particular cell

Dim ws As Worksheet

  ‘Unide sheets with worth of Disguise in cell A2
  For Every ws In ActiveWorkbook.Worksheets
    If ws.Vary(“A2”).Worth = CELLVALUE Then
      ws.Seen = xlSheetVisible
    Finish If
  Subsequent ws

Finish Sub

The Tab Management Add-in

A extra superior choice is to create a sheet with an inventory of the sheet names to be hidden, then have the macro loop by means of the checklist on that sheet. That is precisely what my Tab Management Add-in does, which I present on the finish of the video above.

Tab Management accommodates further options that permit you to rename sheets, change tab colours, and even make the hiding dynamic primarily based on formulation or different circumstances.

Tab Management comes with our Tab Hound Add-in, which is full of further options that make it straightforward to navigate and work with the sheets in your workbook.

Conclusion

I hope this set of macros helps prevent time with hiding and unhiding the identical set of sheets in a workbook.

There are lots of methods to go about this. I just like the tab coloration choice as a result of it is extremely straightforward to implement. You merely change the tab colours of the sheets, then run the macros. This makes it straightforward so as to add or take away sheets from the group.

Do you will have different methods to go about this course of? Please go away a remark beneath with any strategies or suggestions. Thanks a lot! 🙂