MrExcel's Learn Excel #611 - Unhide All Sheets

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 31, 2009.
You can hide several sheets with a single command, but you have to unhide sheets one at a time. Today, using a little VBA, we turn unhiding into a single keystroke. Episode 611 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Welcome back to MrExcel podcast. I'm Bill John.
Today, I have a question that was asked in one of my seminars in Fort Wayne Indiana.
Some person said. You know, hey It's possible to grab several sheets.
You know for example, we can select 4 sheets in group mode and go to "Format" "Sheet" "Hide" and basically hide all those sheets in one step.
But then if I want to unhide those sheets, if I go to "Format" "Sheet" "Unhide" I have to do it one at a time.
There's no way to unhide all of those sheets.
And I said really the best way to do this is with a little bit of VBA code.
Now I've gotten some email from people saying. "Hey, why don't you do a little bit of VBA maybe once a week".
I'm gonna give it a shot today.
So I'm gonna switch over to the VBA editor.
I'll insert a new module and basically, it's just a tiny little bit of code. Well, I'll use Sub unhideAll and we're gonna set up a loop. We're gonna say for each ws In activeworkbook.Worksheets ws.visible = True Next ws Now ws is a variable, that's just a variable that I used but active workbook is the excel name for the open workbook.
The workbook that's currently active in worksheets.
Of course we'll loop through all the worksheets.
What we're basically doing is making each worksheet visible. So now what we'll do is we'll switch back to excel I'll go to Alt F8 that'll give me a list of all of the macros.
I'll choose UnhideAll and maybe assign it to a shortcut key.
You know a lot of the shortcut keys are already taken.
J And K are usually open. I think I'm gonna use U.
Ctrl U to unhide.
Now of course Ctrl U is already underline, and if you're a big fan of using Ctrl U for underline, you wouldn't want to use U.
You would use something else but basically, what we can do now, I have the sheets. Several more are hidden. When I hit Ctrl U, excel will go through very rapidly and unhide all of the sheets.
Tiny little bit of macro code that makes it, this relatively annoying problem very very easy.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top