Adding a table of content for the workbook – it's easy, I promise!

Sometimes workbooks can be very large and difficult to navigate. Only so many tabs fit at the bottom of the screen, and it is difficult to know how long is each worksheet. Excel does not provide a built-in way to add a table of content in a workbook; However, there is a way! In this post, I'll show you how to add a new worksheet to the workbook called "TOC" (TOC). This sample uses Excel 2010.

On the sheet of TOC, column lists the name of each sheet, and includes a hyperlink to the appropriate worksheet. Column b lists (which is the workbook) the number of the worksheet and the number of pages contained in this worksheet (how many printed pages would be). You must use Visual Basic for Applications (VBA), but do not be frightened by the fact that you need to code — is actually very simple, and I'll walk you through it step-by-step.

First, you must add code to the workbook and to do that you need Developer card. If you do not usually work with code in Excel, you probably will not see the development tab in the Ribbon. To view the development: tab

Click the File tab. The Guide below, click Options on. Click to customize the Ribbon. In Customizes the Ribbon, select the check box Developer.

Now you can create a macro:

development tab, in the code group, click Visual Basic.

In the Visual Basic Editor, Enter menu, click form on. In the code window of the module, type or copy the following macro code:

4. In the Visual Basic Editor, Files menu, click close and return to Microsoft Excel.

5. to save the workbook with macro code, select Save File menu and save the file to a macro-enabled workbook (.xlsm).

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

You are almost ready to run the code. You may need to change the macro security settings to enable the macros. To set the security level temporarily to enable all macros, do the following:

development tab, in the code group, click the Macro Security.
In Macro Settings, click enable all macros (not recommended, potentially dangerous code can run) and then click OK on.
Note    To help prevent, it is recommended that you return to potentially malicious code any of the settings that disable all macros after you finish working with macros.

And now you can run the code that creates the table of contents worksheet!

development tab, in the code group, click macro.
In the Macro window, select the Create_TOC macro and click Run on.

View the original article here