//some 3rd party need geo

Excel Index sheet?

  Stubacka 13:27 15 May 03

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 03

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 03

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 03

Magic stuff VoG™, the macro works great - Thanks

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

What is Google Allo? What is Google Duo? Google Allo UK release date rumours and features: Google…

1995-2015: How technology has changed the world in 20 years

These clever designs help visualise a complex intelligence tool

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…