An Easy Way to Organize Excel Tabs 50

Do You Spend Time Doing The Same Task Over and Over?

We often find ourselves doing repetitive tasks in Excel.  If you do any of the following processes, then you've probably felt the frustration of how much time this can take.

• Hiding and unhiding sheets for a monthly reporting package.
• Organizing worksheets by tab color or name.
• Adding multiple sheets to the workbook.
• Renaming a bunch of sheets.

Most of these tasks are NOT the fun part of our job.  They are simply things we have to do, to get a report finished so we can send it to our boss, clients, or audience.

Not only are these tasks boring, but they also take time away from more important projects.  Projects that will help make a bigger impact in your organization and career.

How Can We Automate This?

As I started adding more features to Tab Hound, I quickly realized that I wanted it to be able to automate more of the complex tasks I do with my worksheets.  This included processes like:

• Renaming multiple sheets at one time.
• Re-ordering the sheets in the workbook.
• Changing the colors of all the tabs quickly.
• Hiding and unhiding sheets based on some criteria like the current month.
• Adding a bunch of new sheets with specific names and tab colors.

Some of these were possible in a form like the Tab Hound window, but it is difficult to develop in a way that is easy for the user.  This is where the Tab Control add-in was born.

I wanted to make mass changes to the worksheets in my workbook in a way that was fast and simple.

The Tab Control add-in accomplishes this through a simple 3 step process.

Tab Control – 3 Simple Steps to Automation

Step #1 – Create a List of All Sheets in the Workbook

With the click of a button, Tab Control adds a new sheet to the end of your workbook that contains a list of all the worksheets in that workbook.

Step #2 – Make Changes to the Sheet List

You can then make changes to the sheet list.  This includes changing sheet names, tab colors, tab order, hiding/unhiding sheets, and adding new sheets.

In the example below I changed the sheet names in column B, and also changed the tab colors by filling the cells with color.

Step #3 – Apply Changes to the Workbook

Once you are finished making changes, you simply click the Run Update button and Tab Control will update the workbook with your changes.

It's a quick and easy way to make a lot of changes to the sheets in your file.

A Familiar Environment – The Excel Worksheet

The best part about Tab Control is that you make all these changes in an environment you are familiar with, the Excel worksheet.  You can use Excel's built-in features to make all kinds of changes to your worksheets.  Some of these tools include:

• Find and Replace to quickly change sheet names.
• Filter drop-down menus to sort sheets by color or alphabetical order.
• Formulas and Functions to change sheet names or create conditional logic.
• Autofill and copy/paste to add or change sheets.

Let's take a look at a few examples of how fast you can update your worksheets.

Example #1 – Quickly Update Sheet Names with Find & Replace

Let's say you have 6 worksheets in your workbook that contain the word "pivot" in the sheet name, and you want to change this to say "report" instead.

Typically you would have to double-click on each tab in the workbook and paste the word "report" over the word "pivot".  This would require a lot of mouse clicks!

With Tab Control you could use Excel's Find and Replace feature to find the word "pivot" in the sheet list, and replace it with "report".  Then hit the Update button and all the sheets will instantly be updated with your changes.

Example #2 – Sort Sheets by Tab Color with Filters

Now let's say you want to tidy up your workbook and group all the sheets by the tab color.  This will give it a cleaner look that your audience will appreciate.

If you workbook has more than 10 or 20 sheets, then it will take you a long time to drag the sheets into order by color.

With Tab Control you can quickly sort the sheet list using Excel'sFilter feature.  The fill color of each cell in the sheet list represents the tab color for that sheet.  So you can sort by fill color using the Filter drop-down menu.  You could also sort the sheets in alphabetical order.  Once you have the sheet list sorted, press the Tab Control Update button to instantly update the workbook with your changes.

Example #3 – Hide and Unhide Sheets Based on Criteria

In this example we have a workbook that contains a few sheets for each month of the year.  Every month we need to unhide this month's sheets, and hide the rest of the sheets before we send the file to our audience.

If this is a process you do right now, then you know that you cannot unhide multiple sheets at the same time in Excel.  Instead, you have to unhide each sheet one-by-one, and this can be very time consuming.

With Tab Control, you can automate this entire process to be as simple as the click of a button.  Column C in the TabControl sheet list displays the visible state of each sheet.  A TRUE value means the sheet is visible, FALSE means the sheet is hidden.

You can change the values in column C to control whether the sheet is hidden or visible when the Update process is run.

To make this even faster, you can add a formula to the cells in column C to automatically update the visible state (TRUE/FALSE) based on some criteria.  In the example above I added an IF formula in column C that returns TRUE if column D matches cell G3.  When I change cell G3 to the current month, the values in column C automatically update.

All I have to do is press the Run Update button and Tab Control hides and unhides all the sheets for me.My report is ready to send in just a few clicks!

This may sound complicated at first, but the point is that you can completely customize the Tab Control sheet list to automate your process for hiding and unhidng sheets.  You can get very creative with this and make it extremely easy to update your reports.

No Coding Required

That's right, you don't need to know any VBA code to use Tab Control.

If you wanted to automate any of the examples above, you would typically need to write a macro specific to each process.  Tab Control does all this for you!

It is a very flexible tool with unlimited possibilities.

Unlimited Potential and Flexibility

You might have a workbook right now that contains 50 different tabs, each labeled with an employee's name.  You might have thought, "I wish these tab names were labeled [last name, first name] instead.  It would also be great if they were in alphabetical order.  But there is no way I'm going to spend the time to do all that manually!"

Well Tab Control can tackle this task in about 20 seconds.  Using a few formulas and the sort feature, you could make all these changes to quickly organize your workbook.

Get Tab Control

Currently you can only get Tab Control with the Tab Hound add-in.  Combined, these two add-ins will save you a ton of time with your everyday tasks.

Please click the link below to get Tab Hound and Tab Control.

campbellgoiderearsur1963.blogspot.com

Source: https://www.excelcampus.com/tab-control/

Share this post