using tab name in cells

[Guest]

i have a budget with a bunch of sheets. The first sheet (sheet1) is to be a
summary sheet.

In the summary sheet I'd like to be able to make a cell name dependent on
the tab name of other sheets. That way I only have to change the name in one
place.

For instance in the summary sheet the first column will be all the sheet names

item1 (sheet 2 name)
item2 (sheet 3 name)
item3 (shhet 4 name)

where each item is a sheet name. If there a formula to do it?

 

[Guest]

Here is some code:

Sub listum()
Dim w As Worksheet
Dim s(100) As String
For Each w In Worksheets
w.Activate
s(i) = ActiveSheet.Name
i = i + 1
Next
Sheets(1).Activate
For j = 1 To i
Cells(j, 1) = s(j - 1)
Next
End Sub
It runs thru all the tabs, collecting tab names. Then it goes back to the
first sheet and writes the tabs in the first column.

 

[Guest]

I put it into module 1 but how do I get it to run on opening the book?

 

[JE McGimpsey]

You can get the sheet name in a cell using the technique here:

http://www.mcgimpsey.com/excel/formulae/cell_function.html