Can a macro select a hidden sheet?

Written by Allen Wyatt (last updated August 22, 2019)
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.

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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. Learn more about Allen...

MORE FROM ALLEN

Using the INT Worksheet Function

The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics ...

Discover More

Determining Combinations to Make a Total

If you have a range of cells that contain values, you may wonder which combinations of those cells should be used to meet ...

Discover More

IEEE Citation Format

Different style guides describe different ways of formatting information that appears in a document. One such style guide ...

Discover More

Application.ScreenUpdating = False
Sheets(" ").Visible = True


Sub RunAllMacros()
delete
friss
tabla


End Sub


Sub delete()
'
' Delete Macro
'


'
    Sheets(" ").Select
    Cells.Select
    Selection.ClearContents
End Sub
 
Sub friss()
ActiveWorkbook.RefreshAll
End Sub
Sub tabla()
'
' Tabla Macro
'


'
    Sheets(" ").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), _
        TrailingMinusNumbers:=True
    Columns("A:A").EntireColumn.AutoFit
End Sub
Sheets(" ").Visible = False
Application.ScreenUpdating = False

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

• Article
• 05/05/2022
• 2 minutes to read
• Applies to:Excel 2007, Excel 2003, Excel 2000

In this article

Summary

In Microsoft Excel, you can hide sheets in a workbook so that a user cannot see them. You can hide any type of sheet in a workbook, but you must always leave at least one sheet visible.

More information

Hiding a Sheet Using Menu Commands

To hide a sheet, point to Sheet on the Format menu, and then click Hide. To unhide a sheet, point to Sheet on the Format menu, and then click Unhide. Select the appropriate sheet and then click OK.

Note

You cannot hide module sheets because they appear in the Visual Basic Editor.

Hiding a Sheet with a Visual Basic Macro

You can also hide or unhide a sheet using a Microsoft Visual Basic for Applications macro or procedure. When you use Visual Basic code, you can use the xlVeryHidden property to hide a sheet and keep the Unhide dialog box from listing it. When you do this, the only way to make the sheet visible again is to create another Visual Basic macro.

In a Visual Basic macro, use the Visible property to hide or unhide a sheet. You can set the Visible property to True, False, or xlVeryHidden. True and False have the same effect as using the Unhide or Hide menu commands. The xlVeryHidden argument hides the sheet and also keeps the Unhide dialog box from displaying it.

Sample Visual Basic Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.The following samples show you how to use the Visible property of a Sheet object.

   Sub UnhideSheet()
       Sheets("Sheet1").Visible = True
    End Sub

    Sub HideSheet()
       Sheets("Sheet1").Visible = False
    End Sub

The following sample illustrates how to use the xlVeryHidden argument of the Visible property to hide a worksheet:

    Sub VeryHiddenSheet()
       Sheets("Sheet1").Visible = xlVeryHidden
    End Sub

Can you select a hidden sheet VBA?

Can you reference a hidden sheet in Excel?