Written by Allen Wyatt (last updated March 11, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Logical structures are important in programming, as they allow you to control how the programming statements are ...
Discover MoreAn Excel workbook can contain quite a few different objects. Sometimes those objects can be hidden so that they are not ...
Discover MoreOne of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-03-11 05:16:26
Barry
This is incorrect you can get macros to acces hidden worksheets even "veryhidden" worksheets. What you cab0nnot do is activate or make Selections on hidden worksheets.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments