Excel Automation – Creating an Index sheet in a Workbook

You may have worked on an Excel workbook with just 3 or 4 worksheets in them.

In those workbooks, navigating between worksheets is relatively easy.

Right?

But what if you are working with a humongous dataset with 20 or 40 may be 100 worksheets in the Excel workbook?

What do you do then?

Navigating to the middle sheets or sheets at the end can be a very tedious and time-consuming process.

Isn’t it?

Yes.

Well, in those cases, it is best to create an Index Sheet upfront with a list of all the sheets in the workbook.

Any user can go to any of the sheet in the workbook by clicking on the corresponding index link of that particular worksheet.

But creating this index sheet manually is also an uphill task.

Yeah it is!

Well, you are in luck.

You don’t need to manually create a link to each of the sheet in the workbook.

Instead you can just create it automatically using a macro.

But you don’t know about programming also.

How do you write a macro code for this automation?

Don’t worry don’t worry!

We are still here.

Let us show you how you can create this automated index sheet through an easily available macro code.

Just follow these simple steps below:

Step 1:

Open an Excel workbook that has multiple sheets. Create a new sheet at the front of the workbook and name it “Index Sheet” or anything else you may want. Also, you can format this sheet to put serial numbers if you know the exact number of sheets in the workbook.

Step 2:

Go to the Views tab. Click on the Macros drop down and select View Macros. A macro dialog box opens up.

Step 3:

Let us name the macro in the Macro name option in the macro dialog box. For example, we name the macro name is “CreateIndex”. Once done, Click on Create button.

Step 4:

The macro editor box opens up. There are 2 lines of code already which indicate the macro name is “CreateIndex”. Any code that we write within these 2 lines will become part of the “CreateIndex” macro.

Step 5:

Simply Copy and Paste the below code within the 2 lines of code in the Macro Editor.

Dim sh As Worksheet

Dim cell As Range

For Each sh In ActiveWorkbook.Worksheets

If ActiveSheet.Name <> sh.Name Then

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:= _

“‘” & sh.Name & “‘” & “!A1”, TextToDisplay:=sh.Name

ActiveCell.Offset(1, 0).Select

End If

Next sh

Step 6:

Close the macro editor and come to the Index Sheet in the Excel workbook. Place the cursor where you would like the Sheet names to start appearing from.

Step 7:

Go to the View tab and click on Macros dropdown. Select View Macros again. The Macro dialog box opens up with “CreateIndex” Macro selected. Click on Run.

Step 8:

Voila. The individual sheet names appear one below the other on the Index Sheet. Notice there is a hyperlink to each sheet names. So you can simply click on any of the sheet names to go to the respective sheet.

Step 9 :

Create a hyperlink on each of the individual sheets from 2009 to 2018 to come back to the Index sheet. This way you can create a back and forth navigation from the index sheets to the individual sheets and back again.

This was neat! Simple yet powerful.

What do you think?