Excel Index sheet?

  Stubacka 13:27 15 May 2003

I have created an Excel "Collection" workbook that has a copy of the summary sheets from about 100 other workbooks. Each copy is linked to the original summary so that the copies automatically update when the originals are changed.
Is it possible to create an index worksheet in the "Collection" workbook that will display the names of each tab in the workbook?
I know you can bring data from other sheets onto summary sheets etc. by referring to the cells containing the data but I need to refer to the name on the tab of each sheet.
This is not just because I am lazy and don't want to have to type out the large number of names again but I would like the Index to automatically update if any of the tab names are changed.
A solution to this would save me hours.

  VoG™ 13:38 15 May 2003

Go to click here and download ASAPUtilities.

Open your workbook and on the ASAPUtilities Menu select Sheets/Create an index page with all sheets (clickable)

It takes a while to work if there are many sheets so don't worry if it appears to hang.

  VoG™ 13:58 15 May 2003

That list will not update when the sheet names change. You can use a macro to create the index sheet then run the macro whenever you want to produce an updated list.

Open your workbook, ALT+F11 to open the Visual Basic Editor.

Insert/Module then paste in the following code:

Sub ListSheets()

Dim iSheet As Worksheet, iRow As Integer, sName As String

iRow = 0

Sheets.Add after:=Sheets(Sheets.Count)

For Each iSheet In Worksheets

iRow = iRow + 1

sName = iSheet.Name

ActiveSheet.Range("A" & iRow).Value = sName

Next iSheet

End Sub

ALT+F11 to return to the worksheet.

Now, Tools/Macro/Macros, highlight ListSheets and click the run button.

  Stubacka 17:13 15 May 2003

Magic stuff VoG™, the macro works great - Thanks

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?