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 have made it easier for others to go through data. The first thing I did was to assign names to all the sheets so that they speak for the nature of the data contained within. Next thing was to insert another sheet at the start and create links from that sheet to all other sheets in the file.
To solve the above problem, I created an ‘Index’ sheet. But it is not easy to add hyperlinks 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 an index file using VBA.
Simply open the VBA editor (ALT+F11) and paste the below function there. Run the macro ‘createIndex’ from the Macro list. It will create a new sheet named ‘Index’ at the start of the document. Names of all the sheets are copied to the 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 Do 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