Hide And Unhide Multiple Sheets In Excel: Working with Macros

[@wayneedmondson1065]

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!!

[@ExcelCampus]

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! 🙂🙌

[@wayneedmondson1065]

@@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!!

[@garethmorrall1047]

Hi can you also only display yellow tabs?

[@wayneedmondson1065]

@@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!

[@katerina6495]

Thank you so much John, this is very efficient. Your VBA course is really amazing.

[@ExcelCampus]

Thank you Katerina! I really appreciate your support, and happy to hear you are enjoying The VBA Pro Course. 🙂

[@rajeshmajumdar4999]

Thank you so much👍👍👍

[@ExcelCampus]

Thanks Rajesh! 🙌

[@zzzzzzzzzzzzzzzz9]

Great vid thanks

[@ExcelCampus]

Thanks! :-)

[@kylebuggie]

Great explanation, Jon. I'd be interested in a video about how you make these tutorials.

[@ExcelCampus]

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 (KZbin)? Just curious.

[@kylebuggie]

@@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.

[@ExcelCampus]

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 KZbin. 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!

[@ayeh115]

Hi! where to get the VBA code for this?

[@user-tt9tt6jj1w]

Wow. Its so amazing.. but i have question. How to hide & unhide sheet from another workbook. Please..

[@arazjaff2596]

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 ?

[@ExcelCampus]

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: kzbin.info/www/bejne/nIK7ZYFqmrWbl7c I hope that helps.

[@arazjaff2596]

@@ExcelCampus thank you Jon

[@Michael_Alaska]

Where is the link to the example file?

[@akkintouch]

Dear John, Is there a way to get your personal macro workbook? This is amazing

[@ExcelCampus]

Thanks Avi! Great suggestion! I've shared a lot of my PMW macros on our blog and KZbin 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... 😂

[@akkintouch]

Haha Jon 😆 Hope to hear from you soon

[@fernandoemolina8345]

Would you have a macro for hiding any particular sheet that has "0" in a cell?

[@martinpaulbannister1745]

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

[@heatherr5191]

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

[@ExcelCampus]

Thank you Heather! I know you are a long time user of Tab Hound and Tab Control, and really appreciate your support! 🙌

[@ronakjajpura713]

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 ?

[@KaiWeissmann]

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.

[@jamespyle6398]

Where's the example code link?

[@ExcelCampus]

Hello James, you may download the file from the blog post. Here's the link www.excelcampus.com/vba/unhide-re-hide-multiple-sheets/

[@jamespyle6398]

@@ExcelCampus Thank you