Probably doesn't matter in this case Max, but selecting and activating slows
thing down a bit.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
End With
Next i
End Sub


Gord

On Mon, 18 Aug 2008 16:10:16 -0700, Max wrote:

"wally" wrote:
In a workbook with multiple worksheets, each with a unique name, how would I
create a list of of the worksheets? In addition, I'd like to be able to show
totals from each sheet beside the sheet's name.

In a new sheet,
run the sub below to list all sheetnames in A2 down

Sub ListSheetNames()
Dim wkSht As Worksheet
Range("A2").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub

(Clear A2 which will contain the sheetname of the new sheet)

Then enter the key* cell refs in say, B1 across, eg: E5, E20, K5, K20 etc
*It's assumed that all sheets are identically structured, and that you want
to retrieve the contents of these key cells from each sheet which contain the
"totals", or what-have-you

Place in B3: =INDIRECT("'"&$A3&"'!"&B$1)
Copy B3 across/fill down to populate