Generate index sheet linking all spreadsheets in Excel


Before proceeding, check if you have enabled the macros, i.e., if you don't see DEVELOPER tab in you empty spreadsheet, click on FILE, OPTIONS and Customize Ribbon. You should see a check box on the right hand side, for the DEVELOPER tab, check it and click OK.

Click on DEVELOPER and then Macros, type in some name (eg. import_text), click create.

Paste the below code on the popped window:

  1. Sub CreateLinksToAllSheets()
  2. Dim sh As Worksheet
  3. Dim cell As Range
  4. For Each sh In ActiveWorkbook.Worksheets
  5. If ActiveSheet.Name <> sh.Name Then
  6. ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
  7. "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
  8. ActiveCell.Offset(1, 0).Select
  9. End If
  10. Next sh
  11. End Sub

Once done, click on Macros again, and then Run. This action will automatically populate all cells below the selected cell with sheet name and link to that sheet.