Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Running Macros on Hidden Worksheets.

Running Macros on Hidden Worksheets

by Allen Wyatt
(last updated January 10, 2015)


Macros are often used to process information within a workbook. Your macro can access any cells in the workbook, unless the worksheet containing the cell is hidden. When you hide a worksheet, it is even hidden from normal macro operations.

The upshot of this is that if you want to run a macro and have it access information on a hidden worksheet, you must first "unhide" the worksheet. To do this, you use the following line of code in your macro:

Sheets("My Hidden Sheet").Visible = True

When this line is executed, then the worksheet named My Hidden Sheet will no longer be hidden. It is then easily accessible by using the Selection object or the Select method. When you are later ready to hide the worksheet again (when you are done processing), use this line of code:

Sheets("My Hidden Sheet").Visible = False

Of course, unhiding and later hiding worksheets can cause a lot of flashing on the screen as Excel tries to update its screen display based on the commands executed in your macro. If you want to avoid this, then use the following line of code at the beginning of your macro:

Application.ScreenUpdating = False

With screen updating turned off in this way, nobody will ever know that you unhid a worksheet and later rehid it. Make sure that before ending the macro, however, you set the ScreenUpdating property back to True.

Remember, as well, that the Selection object (and the Select method) are not the only ways to access information. If you rely, instead, on working with ranges (using the Range method or defining an object using the Range method), then you can easily access information on a hidden worksheet without the need to make it visible.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2548) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Running Macros on Hidden Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Fonts Don't Work in Word on New System

When you upgrade from a system with an older version of Word to a system that has a newer version, your fonts may not work in ...

Discover More

Printing a Chart

It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here's how you ...

Discover More

Setting Program Window Size in a Macro

The macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel appears ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Unhiding or Listing All Objects

An Excel workbook can contain quite a few different objects. Sometimes those objects can be hidden so that they are not ...

Discover More

Determining How Many Windows are Open

Does your macro need to know how many windows Excel has open? You can determine it by using the Count property of the Windows ...

Discover More

Quickly Dumping Array Contents

Variable arrays are used quite often in macros. If you use an array once in your macro and then need to reuse it for ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 5?

2017-01-11 16:26:52


Amazing! This worked like a charm.

Thank you!!!

2016-09-27 08:47:16


Nice. Works perfectly. Tanks very much.

2016-09-21 10:58:19


Exactly what I needed. Thank you!

2016-09-05 16:52:58


@ Barry

I tried to go back to the website but I was resticted as I'm using company's internet acess.

Here is my email adress, if you can have it sent to me.

Thanks you much. I appreciate your assistance on that.

2016-09-05 09:23:16



I don't have access to your email address in order to send a test spreadsheet to you. But I've uploaded to the web and you can download from here:

2016-09-04 19:59:47


Thanks Barry for the tip. Ive tried but I came up with errors.

Could you do an example on a workbook of three worksheets and send it via my email or upload it so I can go over it and try incoporate it into my workbook project.

Thank you so much.

2016-09-04 08:17:37



Here's some VBA code that'll do the job. I've used worksheets codenames just in case a User changes a tab name. The Master sheet should have its codename changed to "Master".

The following should be put onto the "ThisWorkbook" codepage:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'make sure all sheets other than the "master" sheet are hidden before closing the workbook
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'make sure all sheets other than the "master" sheet are hidden
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
Dim wsSaved As String
wsSaved = ActiveSheet.Name
Worksheets(wsSaved).Visible = xlSheetVisible
If Master.Name <> wsSaved Then Master.Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_Open()
'make sure all sheets other than the "master" sheet are hidden
'the codename of the master sheet is set to "Master" as this cannot be easily changed by a user.
End Sub

Sub HideSheets()
Dim ws As Worksheet
Master.Visible = xlSheetVisible
For Each ws In Worksheets
If ws.CodeName <> "Master" Then ws.Visible = xlVeryHidden
Next ws
End Sub

Then on the Master worksheet create a button with the following macro for each button don't forget to change the worksheet to be activated for each button. This code will be on the codepage for the Master worksheet:
Private Sub CommandButton1_Click() 'each button will have a unique name
Sheet2.Visible = xlSheetVisible 'change this reference the codename of the required sheet
Sheet2.Activate 'change this reference the codename of the required sheet
Master.Visible = xlSheetVeryHidden
End Sub

Then on each other worksheet create a button with the following code assigned to it on the codepage for that worksheet:
Private Sub CommandButton1_Click() 'button will have a unique name on that worksheet
Master.Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
End Sub

2016-09-03 21:33:03


@ Barry

Thanks Barry.

Currently I'm using hyperlink to selected w/sheets back and fort to the master worksheet. However, I want all the worksheets to be hidden, showing only the mastersheet when opening the workbook. From the masterworksheet, I want to clcik on the name of the sheet (similar concept to hyperlink) through VBA so it can take me straight to the desired w/sheets while at the same time hide the masterhseet. From the desired w/sheet, when I want to return to the master sheet, I will click on the VBA and it will hide the current w/sheet and take me straight to the master sheet again.

Using hyperlink does not work when w/sheets are hidden thats why I want a coding using VBA to be able to do that to hiden w/sheets.


2016-09-02 06:27:29



If you just want to jump to a specific worksheet (or make a specific worksheet active) rather actually hiding the worksheet. You do this without using a macro by using the "hyperlink" function.

To do this select "Insert" then "Hyperlink" then "places in this Document". Choose the worksheet from the list and click OK. Repeat this for each worksheet in your workbook. Then on each worksheet do the same but set the hyperlink to the "master" worksheet.

2016-09-01 07:20:28


I have a school assesment workbook containing many worksheets (30). From the mainsheet,I want to view a worksheet click a VBA command and view the desired worksheet and then click back using VBA command in that active sheet so it can hide the active worksheet and retuns to the mainsheet.

Could someone help with this.

2016-06-09 17:53:18


What if the worksheet is protected (so that others can't unhide the hidden sheet)?

2016-05-26 04:35:37


Thank you! This was exactly what I needed.

2016-01-17 17:05:46


Hi - I only sometimes have a problem accessing hidden worksheets on different computers/different versions of Excel. Does anybody have a good overview of how each version deals with running macros on hidden sheets? Thanks, Anna

2015-12-14 00:29:22

ershad nomani

thanks for can i stop a macro to run only one time.

2015-09-29 04:11:07


Excellent Tip Allen! This works just fine in Excel 2010..

2015-05-22 07:06:35



The following macro will list all the sheets, their names, visible status, and sheet type.

If you only want the number of sheets the code: Sheets.Count will give you this.

Sub wkListSheets()
Dim ws As Integer
Dim wkList As Worksheet
Dim str As String

str = "List" 'change this is a sheet called "list" already exists

On Error Resume Next
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Set wkList = Worksheets.Add(before:=Sheets(1))
wkList.Name = str
With wkList.Range("A1:C1")
.Value = Array("Sheet Name", "Visible", "Sheet Type")
.Font.Bold = True
.Font.Underline = xlSingle
End With

For ws = 2 To Sheets.Count
wkList.Cells(ws, 1) = Sheets(ws).Name
Select Case Sheets(ws).Visible
Case xlSheetVisible
wkList.Cells(ws, 2) = "Visible"
Case xlSheetHidden
wkList.Cells(ws, 2) = "Hidden"
Case xlSheetVeryHidden
wkList.Cells(ws, 2) = "Very Hidden"
Case Else
wkList.Cells(ws, 2) = Sheets(ws).Visible
End Select
Select Case TypeName(Sheets(ws))
Case xlWorksheet
wkList.Cells(ws, 3) = "Worksheet"
Case xlChart
wkList.Cells(ws, 3) = "Chart"
Case xlExcel4MacroSheet, xlExcel4IntlMacroSheet
wkList.Cells(ws, 3) = "Excel4 Macro Sheet"
Case Else
wkList.Cells(ws, 3) = TypeName(Sheets(ws))
End Select
Next ws
Set wkList = Nothing
End Sub

Please note: if there is already a sheet named "List" it will be deleted, or change the "str" variable to another name as shown above.

2015-05-21 18:42:11


Is there a way to show a list of hidden sheets -- even if I can't see the sheets.

Say, for example, I want a count of all the sheets in the workbook (vis. and non-vis.)

2015-03-26 06:32:12



What error code/message are you getting?

The most likely error is that the object you are trying to make visible no longer exists. E.G. if you are trying to make a worksheet visible by referring to it using its tab name, if someone changes the tab name (or deletes that worksheet) then you will get an error if this change hasn't been reflected in the macro as well.

If worksheet name changes are possible then it is better to the worksheet's "codename" which cannot be changed.

2015-03-25 13:23:29


What will cause an error in the Visible = True line of code all of a sudden?

2015-03-25 10:42:24


"When you hide a worksheet, it is even hidden from normal macro operations."
This is not strictly true a macro can access cells on a hidden or even a "very hidden" worksheet but what the macro cannot do is "Select" a cell on a hidden worksheet (this will cause an error).

It is not good programming practice to use the Selection object or the Select method anyway.

2015-01-23 13:01:54


Thanks for the tip and the helpful comments! Very useful for updating reports that contain sheets people should not be modifying.

2015-01-16 08:39:06


The Visible property of the Worksheet object isn't boolean (i.e. true or false only).
The property can have any of the following values:
xlSheetHidden (or False or 0)
xlSheetVisible (or True or -1)
xlSheetVeryHidden (2)

You can use either the actual values or the enumerations, I prefer the enumerations as it is more obvious when I have to go back over the code what the macro is doing.

2015-01-15 20:08:45


I'm curious since the Hide/Unhide has become so common, is there a way to move a sheet to the 'VeryHidden = True' in a macro?

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.