how do i link the names of tabs in a workbook to a summary page?

[Andrea O]

Hi

I am working on a spreadsheet with information on different items on
separate tabs. The first tab is a summary sheet where information from the
tabs will be entered onto a table.

I am trying to link the names of the tabs to a column on the summary tab. I
have tried using ='click on tab' . This works with inserting a value so that
the name of the tab appears, but then as it is just a value I connot
manipulate it in any way, eg - if I change the name of the tab it does not
change on the summary sheet!

This means I will have to potentially enter the names of hundreds of tabs
manually :-( any help would be greatly appreciated.

 

[Daryl S]

Andrea -

The macro below will list all tab names in column A starting in cell A3 in a
worksheet called "Summary". You can adjust as necessary. Remember that if
the order of the sheets is changed, then so will the order of the results.
If "Summary" is always the first worksheet, then you can start SheetIndex at
2 so it won't put "Summary" in the list of sheets. To change the starting
row, adjust the Range statement. You can rename the macro to what you want.

Sub ListSheets()
Dim SheetIndex As Integer

Sheets("Summary").Select
SheetIndex = 1
Do While SheetIndex <= Sheets.Count
Range("A" & (SheetIndex + 2)).Value = Sheets(SheetIndex).Name
SheetIndex = SheetIndex + 1
Loop
End Sub