List of worksheets
 

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.

List of worksheets
 

"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
---

List of worksheets
 

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
---

List of worksheets
 

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.

List of worksheets
 

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

List of worksheets
 

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

List of worksheets
 

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

List of worksheets
 

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

List of worksheets
 

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

List of worksheets
 

Thanks for the feedback.

Gord

On Sun, 31 Aug 2008 09:19:00 -0700, wally
wrote:

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