How to Create Index Sheet Hyper-linked to Other Sheets

Some days ago, I was working with some data which was distributed in about 18 sheets within a single Excel file. I was supposed to make calculations and then present that file to senior management. Now presentation was important as well. I must had made it easier for others to go through data. First thing i did was obviously name all the sheets so that they speak for the nature of data contained within. Next thing was to insert another sheet at start and create links from that sheet to all other sheets in the file.

To solve above problem, I created an ‘Index’ sheet. But its not easy to add hyper-links to 18 other sheets manually. It would have taken a lot of time and energy as well.

I wrote a small code snippet to create index file using VBA.

Simply open VBA editor (ALT+F11) and paste below function there. Run the macro ‘createIndex’ from Macro list. It will create a new sheet named ‘Index’ at start of the document. Names of all the sheets are copied to newly created sheet and those names are linked to the sheet as well. Please make sure that when you run this macro, there is no sheet named Index already.

Sub createIndex()
Dim i As Integer
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "Index"
counter = Sheets.Count
i = 2
Range("A" & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
i = i + 1
Loop Until i = counter + 1
End Sub

Receive Excel Tips and Functions in your email

Sign up for our Newsletter -provide your email address to receive updates in your inbox.

Speak Your Mind