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.

"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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

Thanks, that seems to do the job. Appreciate your help.

"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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---
"wally" wrote in message
...
Thanks, that seems to do the job. Appreciate your help.

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

Thanks for the tune-up, Gord !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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
Please advise how this macro can 'CreateListOfSheetsOnLastSheet'.
Wally

"Gord Dibben" wrote:

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

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


Gord

On Sat, 30 Aug 2008 18:24:00 -0700, wally
wrote:

Gord
Please advise how this macro can 'CreateListOfSheetsOnLastSheet'.
Wally

"Gord Dibben" wrote:

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

Thanks Gord. Appreciate what you do and the advice you give.
Wally

"Gord Dibben" wrote:

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


Gord

On Sat, 30 Aug 2008 18:24:00 -0700, wally
wrote:

Gord
Please advise how this macro can 'CreateListOfSheetsOnLastSheet'.
Wally

"Gord Dibben" wrote:

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