Hide And Unhide Multiple Sheets In Excel: Working with Macros
Вставка
- Опубліковано 12 вер 2024
- Learn how to use a set of macros to automate the process of unhiding and hiding (rehiding) multiple sheets in Excel.
-- LINKS --
DOWNLOAD THE WORKBOOK ► www.excelcampu...
** JOIN OUR COMPREHENSIVE EXCEL TRAINING PROGRAM**
www.excelcampu...
--
~ Learn my BLUEPRINT for Excel including when to use what tool (FREE TRAINING SESSION) www.excelcampu... ~
~Become an Excel Campus Insider (100% free) to access advanced workshops, bonus training, and weekly Excel tips: www.excelcampu...
In this video I explain how the macro hides and unhides sheets that have a specific tab color. The macro loops through all sheets and changes the visible property to visible or hidden if the sheet is a certain tab color.
This is great if you have a file that you update periodically and send to other users. You might need to unhide sheets to update data and formulas, then rehide those sheets before distributing the file.
I recommend adding the code module to your Personal Macro Workbook, and creating a custom ribbon with macro buttons. This allows you to run the macros on any open workbook on your computer.
**********
EXCEL FRIENDLY ACCESSORIES WE USE (Affiliate Links When Possible): LOGITECH KEYBOARD
Check out all the tech we use and recommend at www.excelcampu...
**********
-- Related Content --
- How to setup your Personal Macro Workbook: • The Personal Macro Wor...
- Create Custom Ribbon with Macro Buttons: • How to Add Macro Butto...
- Copy or Import VBA Code to a Different Workbook: • How To Import Or Copy ...
- The For Next Loop Explained: www.excelcampu...
- Tab Control Add-in Overview Video: • Organize and Automate ...
- Get Tab Hound and Tab Control Add-ins: www.excelcampu...
#ExcelCampus #MsExcel
Hi Jon.. great video. After taking your VBA course a few months back, I set up something similar (hide/unhide worksheets based on tab color) and have been using it to great effect since. I like some of the details you added, as well as the Tab Control component of Tab Hound.. excellent. Also, good advice on using standard colors for tabs to avoid conflicts with code set to theme colors that might change in the future. Thanks again for all the tips, tricks and great videos. I learn something new from you every time. Also.. a plug for your VBA course.. anyone who wants to learn VBA.. take the course.. it's great! Thumbs up!!
THANK YOU Wayne! Your thirst for knowledge and growth is inspiring. I truly appreciate your support and endorsement of The VBA Pro Course. Thanks again! 🙂🙌
@@ExcelCampus Thanks Jon.. credit to you, Mike Girvin, Leila G., Oz, Alan Murray and a few others for the inspiration to move forward in my learning. I sat complacent for many years with EXCEL just using SUM and IF and thinking I knew all I needed to know. EXCEL is such a full and feature rich program. It is a pleasure to learn each new facet and figure out how to use it for fun, as well as profit. Your site and resources are top notch and I recommend them often. Thanks again for all that you offer to the community.. both free and paid.. all excellent and very much appreciated. Thumbs up!!
Hi can you also only display yellow tabs?
@@garethmorrall1047 Hi Gareth. Yes, assuming you use standard vbYellow as the tab color, then the following code will hide all but the Yellow colored tabs:
Sub Hide_Non_Yellow_Tab_Sheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Tab.Color vbYellow Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub
Hope this helps. Good luck!
Thank you so much John, this is very efficient. Your VBA course is really amazing.
Thank you Katerina! I really appreciate your support, and happy to hear you are enjoying The VBA Pro Course. 🙂
Thank you so much👍👍👍
Thanks Rajesh! 🙌
Great explanation, Jon. I'd be interested in a video about how you make these tutorials.
Thanks Kyle! Are you interested in learning the tools I use to record the videos and produce them? And the process behind that? Are you looking to create video tutorials to share with co-workers, or for the public (UA-cam)? Just curious.
@@ExcelCampus Hi Jon, I am exploring using video tutorials with my team at work. I really like the format you and some other channels (e.g., Khan Academy, Guy in a Cube) use to articulate complex techniques, sequences, etc. I assume you are using something like Camtasia Studio. For an Excel/VBA solution, I just thought a guided video would be an interesting approach to training/documentation.
Thanks @@kylebuggie! Yes, I am using Camtasia to record, edit, and produce the videos. We use Snagit to do all screen captures (another tool by Techsmith). And a lot of the image editing is actually done in Excel or PowerPoint. Those apps have a great shapes/layers engine.
I also made tutorial/training videos at my job before doing them publicly on UA-cam. There are other tools you can use that are free or cheaper than Camtasia, but much more limited on the editing side. Jing and Loom are two that come to mind, but there are a lot of others.
I've thought about creating training on creating tutorial videos, and will add this to my list for future projects. I just looked in my Camtasia folder and have recorded almost 3,000 video files. So I guess I have a bit of experience with this... 😂
Let me know if you have any questions or if there is anything in particular you'd like me to cover. Thanks again!
Great vid thanks
Thanks! :-)
Wow. Its so amazing.. but i have question. How to hide & unhide sheet from another workbook. Please..
this is a great macro, but believe me Tab Hound / Tab Control add-in is very worth the investment if you need to delete or rearrange worksheets very often
Thank you Heather! I know you are a long time user of Tab Hound and Tab Control, and really appreciate your support! 🙌
Dear John,
Is there a way to get your personal macro workbook?
This is amazing
Thanks Avi! Great suggestion! I've shared a lot of my PMW macros on our blog and UA-cam channel. Here is a list of all VBA related posts on our blog. www.excelcampus.com/category/vba/
However, I've had this request a few times recently and I'll put something together to share. I really just need to clean my house (PMW) before inviting you in. There are probably some messy (confusing) parts... 😂
Haha Jon 😆
Hope to hear from you soon
Would you have a macro for hiding any particular sheet that has "0" in a cell?
thank you john . but i want to ask in another thing that if we can move any data from sheet to another by formula , is there any way to change the color like data ( for example in sheet 1 i have a data in a5 , in sheet 2 by formula i move the same data ,but when i change the the data and cell color ( a5 ) its only the data chang in sheet 2 without change the color ?
Hi Araz,
If I understand correctly, I believe you want to use Paste Special > Formulas only. This can be done from the Paste Special menu in Excel and we can also write VBA code to do the same. I do have a series of videos and copy & paste with VBA that includes the Paste Special Method. Here is a link to part 3 of the series that covers it: ua-cam.com/video/fQX4N4ePhaU/v-deo.html
I hope that helps.
@@ExcelCampus thank you Jon
Hi! where to get the VBA code for this?
Hi - can you please advise how hide and show worksheets using your VBA code (tab colour) works on a protected workbook and protected worksheets. Everything I have tried, I receive Excel error, I have tried to add a unprotect code before running tab colour, then reapplying protection, but this fails each time. Thank you in advance, Martin
Where is the link to the example file?
Hi John, I want to run macros on a hidden sheet. Hidden sheet is only to help as source data, and will never get displayed. Any suggestion ?
Have you solved the problem already? I'd say you can unhide the sheet at the beginning of the the macro and unhide it at the end.
Where's the example code link?
Hello James, you may download the file from the blog post. Here's the link www.excelcampus.com/vba/unhide-re-hide-multiple-sheets/
@@ExcelCampus Thank you