How To Automate Common Processes in Excel with Tab Control

Do you spend time hiding and unhiding multiple sheets for a monthly reporting package? Learn how the Tab Control add-in can automate this process and save time with common worksheet tasks.

In the last two videos I explained how the Tab Hound and Tab Control add-ins can save you a ton of time with your everyday Excel tasks.  Here are links to the two videos in case you missed them.

Video #1 – Quickly Find Worksheets & Unhide Multiple Sheets

Video #2 – The Fastest Way to Copy a Sheet – Plus Tab Control

These videos also contain a lot of Excel tips and shortcuts, and I hope you can learn something from them.

In this new video I am going to demonstrate how you can automate some common tasks with the Tab Control add-in.

You will learn:

  • How to add multiple sheets to your workbook from a list of sheet names.
  • How to re-order your sheets quickly with drag-and-drop.
  • How to automate the process of hiding and unhiding multiple sheets for a monthly reporting package.

I also answer questions from the last video, including when Tab Hound and Tab Control will be available.

The Future of Tab Control

In the video above I give a simple example of how Tab Control can be used to automate the process of hiding and unhiding sheets.    This is a common process that many of us do for periodic reports.

This is just one example of how Tab Control can be used for process automation.  It really only scratches the surface of what is possible with this tool.

Using a spreadsheet (TabControl sheet) for the inputs gives us a lot of possibilities for different features and uses.  Based on your suggestions, I'm currently working on ways to split (export) sheets into different files, apply detailed protection properties, copying sheets, and more.

I'm looking forward to learning how you use this tool.  I know we will be able to create ways to help automate some of your repetitive tasks.

Please leave a comment below with any questions or suggestions.

Thank you! 🙂

Tab Hound and Tab Control are Now Available

The Tab Hound and Tab Control Add-ins are now available.  Please click the link below to learn more about it.

Tab Hound Box 150x150

Get Tab Hound

12 comments

Your email address will not be published. Required fields are marked *

  • Jon –
    It’s looking better all the time.
    I almost always want the footers in my printed tabs to have &Filename;&tab at the left, &page of &pages in the center, and printed &date &time at the right.
    I know how to copy those settings from an exiting sheet to another by selecting both tabs, going to the ribbon for Page Layout, clicking the little arrow at the bottom right and then OK. Do you think adding “Copy Page Layout” feature as a one-click to Tab Hound is a good idea?

    • Hi Dan,
      That’s definitely a good idea. Tab Hound has a select sheets button that allows you to select multiple sheets. So you could use it to select the sheets quickly before copying the page layouts. But I like the idea of having a dedicated button. I’ll put it on the list… 🙂

  • Loved the new feature Tab Control. I noted that you set up the link to work quarters and that is awesome however what about semi-annual or annual reports? Would one just extend the pull-down to include the other options (i.e., 1st half, 2nd half, ALL)

    • Great question Dan. Everything in that Tab Control sheet was formula driven. And those formulas can be completely customized by you. Sorry, I don’t know that I made that clear in the video.

      But you could put any formula in column C (Visible) of the Tab Control window. The formula just has to return a TRUE or FALSE value.

      That means you could definitely have formulas for 1st Half/2nd Half. You could also set it up to accommodate any time period like weeks, months, etc. You could also set it up for different categories or regions. The possibilities are endless and it is can be completely customized by you.

      I will create more instructional videos to walk through the process in more detail. I’m planning to have a dedicated web page for Tab Control where you can leave comments to ask questions, and also see what others have asked.

  • Hi Jon. Looking good. Suggestion re your goal to do things the fastest way with least amount of actions: Do away with the need to click the Update wherever it makes sense, and do away for the need for the user to dismiss the ‘Done’ message. Especially for things like reordering the table, or when the user selects from the dv list. Just put an event handler on the TabControl sheet…if something gets changed, run the update. Just add a ‘defer update’ option in case a user actually has the sheet open and is making multiple changes.

    Re the “Done” messages: For me they are largely redundant – I know the sheets were reordered or whatever because i clicked the button, and in fact can review the sheet tabs if I really want. Having to dismiss a “Done” message forces me to click or push Enter.

    Saves two clicks overall. And also suggest you expose the functions to VBA developers so they can just call these in need as part of other routines. (Maybe you have).

    • Thanks for all the great suggestions Jeff! I agree with the redundancy of the “done” messages. I’m planning to add some Options where you can turn those off.

      I like your idea of using the event handler for any sheet changes. That would definitely make it faster!

      Thanks again. I love to save clicks… 🙂

  • Jon
    Some time I need to copy items in a worksheet to another workbook that contain formulas, this is what usually happens.
    The range you are pasting contains formulas that cannot be pasted into this instance of excel.
    I use excel to collect data from a data accusation system in a power plant, excel don’t like to copy and paste the formulas from one book to another.
    I most often have to go in and manually write the formulas into the new workbook.
    Can tab hound be setup to copy the formulas to other workbooks?

    • Hi Wes,
      You can get that error message if you have multiple instances of Excel open at the same time, and you are trying to copy data from one instance to another. It can be difficult to tell when you have multiple instances open. What version of Excel and Windows are you using? I can provide some info or screenshots that will help you prevent this problem.

      Thank you,
      Jon

  • Thank you for sharing wonderful knowledge. Things you sheared really helping a lot.
    I m here to submit a request if I may
    I m looking for a complete application built in Excel for example Sales Excel
    Sheet 1 content only buttons like –
    Add new sale – opens up a form to add a sales
    Search – same as you have Find All tutorial
    And anything you can add

    This way not only we all will learn the complete excel workflow also will make us understand how we can implement our daily task in excel
    Thank you and looking forward for your early and favorable response

  • The main purpose of automation is to improve the quality of process execution. An automated process is more stable than a manual process. In many cases, process automation can increase productivity, reduce process time, reduce costs, increase accuracy, and increase stability of operations. A specialized application will help in this process”https://fluix.io/solutions-workflow-automation

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter