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

Running Macros on Hidden Worksheets

by Allen Wyatt
(last updated May 5, 2016)

20

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 regular macro commands. 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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9329) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel 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. ...

MORE FROM ALLEN

Pasting a Comment into Your Document

When developing a document, you may end up with all sorts of comments that you need to deal with. One common task is to copy ...

Discover More

Inserting the Saved Date In a Header or Footer

When preparing a worksheet for printing, you may want to include in the header or footer the last date the workbook was ...

Discover More

End-of-Month Calculations

Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering the ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Copying Pictures with a Macro

Copying information using a macro is rather simple, although there are multiple ways you can do the copying. The most ...

Discover More

Stopping a Checked Box from being Unchecked

When creating user forms for use in Excel, you are provided with a range of controls you can add, including check boxes. If ...

Discover More

Determining the Hour of the Day

Need to know the current hour of the day? You can derive the information in your macros by using the Hour function, as ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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. Maximum image size is 6Mpixels. 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 seven minus 2?

2017-01-11 10:33:00

Caleb

Thanks! This helped me fix my issues and learning how to stop the screen updating really sped up my macro as it used less processing power. Helped alot!


2016-08-19 23:37:38

Peter

I have a different problem. Excel "Very well hidden" sheets complete with password protection can be viewed without any problem over a smartphone. Does anyone know a way around this?


2016-07-21 05:44:14

Barry

@Ramanathan

Hiding the worksheet is easy you can just create a button on each worksheet you want to be able to hide and assign it the macro:

Sub HideSheet()
Activesheet.visible=xlSheetVeryHidden
End Sub
put this macro in a code module.

Unhiding is not so easy as you cannot see the hidden sheet (duh!!), this will need to be done via a control worksheet which cannot be hidden with a button for each other worksheet which when clicked will execute a short macro to unhide that selected sheet and make it active.

Sub UnhideSheet()
With Thisworkbook.Worksheets("SheetName") 'use the actual worksheet name instead of SheetName
.Visible = xlSheetVisible
.Activate
End With
End Sub

A more elegant way might be to list the hidden worksheets in a ListBox so the User selects the Worksheets from the drop-down list then clicks a button to activate the select worksheet(s). This could be on a master/control worksheet, in a menu, or floating in modeless Userform.


2016-07-21 01:42:34

Ramanathan

I Want New codings to Hide or un hide the sheets by clicking in the box which is im going to set in excel sheet.


2016-06-10 11:31:53

Whatever

Thank you!!!


2016-03-24 05:31:44

Barry

@Dee

First and foremost make sure that you are saving the workbook as a macro-enabled workbook (with the extension ".xlsm") if not then the macros will not be saved. Excel does give a warning when saving but it is too easy to just select the default of saving without macros.

If this is not then case then look at when recording a macro you are initially required to name the macro, any shortcut key, AND crucially where it is to be stored. The storage choices are:

a. Personal Macro Workbook
b. New Workbook
c. This Workbook

It is likely that you've stored it in the wrong place. Which is the best place to store it depends on how you will be using the workbook/distributing the workbook, and is a whole discussion in its own right. As a newbie to macros I suggest you select "This Workbook" for simplicity.

The other possiblity is that it the macro is being stored in a different module. Re-opening a workbook and creating a new macro will create a new module, and although the macro is still there it is in a different module.


2016-03-23 08:48:08

dee

Hi,

I am also very new to macros, though I have been using excel for over a decade - I just felt that I never needed macros and have been dodging the bullet for awhile. However, I realise that I need it and Im stuck at the 1st huddle.

I have recorded a macro, which works fine. My issue is every time I close and reopen the file and try to run the macro, its no where to be found.

How do I ensure that the macro is visible to be used in the workbook all the time, please help....


2015-12-01 03:43:48

amit goyal

Great help, today prepared my first VBA macro based dashboard and got soe help from here.. thanks


2015-09-29 06:15:20

Barry

@M3NT4L

You can replace the code:

Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste

With a single line of code:

Sheets("Repair Evaluation Data").Columns("C:C").Copy _
Destination:=Sheets("Repair Roster").Range("L1")

(this can be all on one line but I broke the line for display purposes)

As this does not use the Selection object or method the display will not change.


2015-07-30 12:00:00

M3NT4L

I"m new to macros in excel and finding that they are extremely helpful, but have run into a knowledge issue.. (my lack of it :) )

Below is a macro that I had running perfectly, then I tried to add to it using the 'record macro' function hence all of the 'selects' The first portion is filter a table based on a cell value 'c4:e4' Runs fine....

The second portion I'm trying to run is a copy of a range and paste it to another sheet... It works, but it does not hide the screen switches and I need to hide the sheets.

Any help is appreciated! Thanks



Private Sub Worksheet_Change(ByVal Target As Range)

Const DropDown = "c4"

Const TableSheet = "Repair Evaluation Data"

Const TableRange = "A1"
If Not Intersect(Range(DropDown), Target) Is Nothing Then
Application.EnableEvents = False
If Range(DropDown).Value = "" Then
Worksheets(TableSheet).ShowAllData
Else
Worksheets(TableSheet).Range(TableRange).AutoFilter _
Field:=8, Criteria1:=Range(DropDown).Value
End If
End If
Application.EnableEvents = False
Range("C4:E4").Select
Sheets("Repair Evaluation Data").Select
Sheets("Repair Evaluation Data").Columns("C:C").Select
Selection.Copy
Sheets("Repair Roster").Select
Sheets("Repair Roster").Range("L1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$L$1:$L$1041020").RemoveDuplicates Columns:=1, Header:= _
xlYes
Sheets("Repair Center Dashboard").Select
Application.EnableEvents = True

End Sub


2015-04-10 08:32:08

Lokesh Goyal

As chuck mentioned that we can run the macro with hidden sheets, I tried it an the macro ran perfectly well.However when I tried to run the macro line by line using the debugger it gave me error on the line which accessed hidden sheet.
Does any one know why is this so


2013-05-02 09:01:59

chuck

I ran a small test to verify: In fact, you can select cells on a hidden sheet... if you activate the sheet first.

So, apparently the error does not come from trying to select a cell on a sheet that is hidden, but rather, from trying to select a cell on a sheet that is not the active sheet.

Therefore, if you really want to select cells on a hidden sheet for some reason, then you can activate that sheet (but leave it hidden).

I do this in a case where I run a large query and subsequent calculations in the background (on another sheet), but I don't want it to affect the user's screen. Now, I normally don't use the select method at any time on this hidden sheet, but I ran a small test to prove that it works without setting an error.


2013-05-01 05:53:08

JMJ

@Bryan: Thanks for confirming what I thought, and explaining why it works!


2013-04-30 16:13:20

Bryan

@JMJ: You actually don't need to reset Application.ScreenUpdating to True at the end, because it is automatically set to True when code stops executing (a few other settings reset as well... Application.DisplayAlerts comes to mind).

The same can be said of local object variables (ranges, worksheets, etc); some people consider it "good coding practice" to clear out and reset everything at the end of the macro, but other people point out the VBA garbage cleaner is going to do this for you anyway!


2013-04-30 01:22:01

Dave Unger

Same here, I have no problems running my macros on hidden sheets. I almost never select any ranges, so it's not a problem.

Regards


2013-04-29 13:03:08

JMJ

You said "Make sure that before ending the macro, however, you set the ScreenUpdating property back to True."

In fact, I never, ever, noticed any difference when doing so or omitting this instruction: When the macro ends, the screen can be refreshed anyway!


2013-04-29 09:49:00

chuck

You can even perform the equivalent of pressing a button on a hidden sheet (i.e., run the button's macro) without ever unhiding the sheet. To "press a button" on a hidden sheet, use something like:
Set OriginalWorksheet = ActiveSheet
Sheet3.Activate
Application.Run ("'" & ThisWorkbook.Name & "'!" & "Sheet3.CommandButton1_Click")
OriginalWorksheet.Activate

Note that in the Application.Run command, you must use the sheet's code name that Excel gave it, not it's visible name that you gave it.

Interstingly, although you can't select cells in a hidden sheet, you can activate the sheet while it is still hidden. Many of my subroutines are written generically, so that the same code can run on different sheets. To do this, I rely heavily on the "Activesheet" object. So, for me it was important to activate the sheet before running the code so that it runs on the correct sheet. For simpler tasks, that may not be necessary. However, remember that if you don't explicitly specify the sheet, VBA often defaults to the Activesheet.


2013-04-29 08:28:27

Bryan

The macro recorder is excellent for figuring out how to do things you didn't know how to do, but one of the greatest downsides is you think you need to select everything to use it!


2013-04-29 07:25:46

Barry Fitzpatrick

I, too, have macros that access cell values on hidden worksheets without any problems. I often have a web query that returns results to a hidden calculation page before then extracting the retrieved data.

BUT as Rik says your macros/VBA code must NOT use the "Select" method for accessing a cell or range of cells. Otherwise this will resulting in a run-time error.


2012-08-22 04:03:34

Rik Smeesters

My macros work with very hidden sheets with no problems but I don's select
these sheets or cells on in.
I use statements like:
var1=range(c1,c2).value
for i=lbound(var,1) to ubound(var,2)
var(i,1)=sqr(var(i,1))
next i
range(c1,c2).formula=var1
range(c1,c2).sort ...
Kind regards


This Site

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

Newest Tips
Subscribe

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.