Create table of contents for Excel spreadsheets
You want to create a table of contents on the first sheet in a larger Excel workbook. For this you need a list with the names of the individual tables. A macro helps you do this.
Create table of contents using an Excel macro
Since you cannot directly access the sheet names via the table function, it is best to do this with a macro.
- To do this, execute the "Tools | Macro | Macros" command in the open workbook. Type in a name like "Table List" for the new macro and click "Create".
- Enter the following program code in the VBA editor:
- Sub table list ()
- Sheets (1) .Select
- Sheets.Add
- Sheets (1) .Name = "Content"
- n = 1
- For Each i In Worksheets
- If i.Index 1 Then Range ("a" & n - 1) .Value = i.Name
- n = n + 1
- Next i
- End Sub
Exit the VBA editor with the command "File | Close and back to Microsoft Excel". To run the macro, call "Tools | Macro | Macros". In the following dialog, select your macro "Table list" and click on "Execute".
Create new worksheet with table of contents
Excel then creates an additional worksheet at the beginning of the workbook and creates an overview of all the tables contained in the folder.
- Instead of on a new sheet, you can have the list created in an existing table. To do this, change the second line of the macro: For example, enter >> Sheets ("Overview Sheet"). Select <<. You can use the name of the desired sheet as an argument in the brackets.
- Alternatively, choose a sequential number that corresponds to the rank of the sheet in the workbook. Finally, delete the following two lines in the macro that insert the additional sheet.