How do I generate a list of the tabs/worksheets from a workbook?
 

I have a workbook with numerous tabs/worksheets...I have created a summary
sheet that links to each worksheet.
First of all I would like to produce a list of all the tabs in the workbook
to verify that i have included every sheet. I know this is a bit archaic but
I want to start here.
Secondly, I would like to know how to create a new sheet that will
automatically detect each tab, list the tab names in a single row across, and
pull specific data from each tab.

I am not proficient with excel macros or visual basic...but i can folow
simple instructions easily.

here is a simple example of what I would like to do:

Sheet 'A'
Price 150
Estimate 200
Recommendation Buy

Sheet 'B'
Price 250
Estimate 200
Recommendation Sell

Sheet 'Summary'
A B
Price 150 250
Estimate 200 200
Recommendation Buy Sell

I want the summary sheet to automatically update if I add a new sheet, say 'C'
The data it will be looking for will be in the same cells of each sheet.

I know i can do this by just inserting columns and copying the links
formulas from the adjacent cells...however, the workbook is constantly
updated, and new sheets added and deleted frequently...I would rather if
there was someway to update the summary sheet automatically.

How do I generate a list of the tabs/worksheets from a workbook?
 

Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub


--
Gary''s Student
gsnu200702


"tjennings" wrote:

I have a workbook with numerous tabs/worksheets...I have created a summary
sheet that links to each worksheet.
First of all I would like to produce a list of all the tabs in the workbook
to verify that i have included every sheet. I know this is a bit archaic but
I want to start here.
Secondly, I would like to know how to create a new sheet that will
automatically detect each tab, list the tab names in a single row across, and
pull specific data from each tab.

I am not proficient with excel macros or visual basic...but i can folow
simple instructions easily.

here is a simple example of what I would like to do:

Sheet 'A'
Price 150
Estimate 200
Recommendation Buy

Sheet 'B'
Price 250
Estimate 200
Recommendation Sell

Sheet 'Summary'
A B
Price 150 250
Estimate 200 200
Recommendation Buy Sell

I want the summary sheet to automatically update if I add a new sheet, say 'C'
The data it will be looking for will be in the same cells of each sheet.

I know i can do this by just inserting columns and copying the links
formulas from the adjacent cells...however, the workbook is constantly
updated, and new sheets added and deleted frequently...I would rather if
there was someway to update the summary sheet automatically.

How do I generate a list of the tabs/worksheets from a workbook?
 

You can write code to copy that data for new sheets, but there is no delete
sheet event so you cannot trap that.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"tjennings" wrote in message
...
I have a workbook with numerous tabs/worksheets...I have created a summary
sheet that links to each worksheet.
First of all I would like to produce a list of all the tabs in the
workbook
to verify that i have included every sheet. I know this is a bit archaic
but
I want to start here.
Secondly, I would like to know how to create a new sheet that will
automatically detect each tab, list the tab names in a single row across,
and
pull specific data from each tab.

I am not proficient with excel macros or visual basic...but i can folow
simple instructions easily.

here is a simple example of what I would like to do:

Sheet 'A'
Price 150
Estimate 200
Recommendation Buy

Sheet 'B'
Price 250
Estimate 200
Recommendation Sell

Sheet 'Summary'
A B
Price 150 250
Estimate 200 200
Recommendation Buy Sell

I want the summary sheet to automatically update if I add a new sheet, say
'C'
The data it will be looking for will be in the same cells of each sheet.

I know i can do this by just inserting columns and copying the links
formulas from the adjacent cells...however, the workbook is constantly
updated, and new sheets added and deleted frequently...I would rather if
there was someway to update the summary sheet automatically.

How do I generate a list of the tabs/worksheets from a workbook?
 

Brilliant! Just what I was looking for! And I didn't even have to ask the questions. Thanks.

Philip

Gary''s Student wrote:
Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub

How do I generate a list of the tabs/worksheets from a workboo
 

Glad I could be of help.
--
Gary''s Student
gsnu200709


"Philip" wrote:

Brilliant! Just what I was looking for! And I didn't even have to ask the questions. Thanks.

Philip

Gary''s Student wrote:
Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub

How do I generate a list of the tabs/worksheets from a workboo
 

This sounds very similar to what I need to do... but how did you create the
links to each sheet?

"Gary''s Student" wrote:

Glad I could be of help.
--
Gary''s Student
gsnu200709


"Philip" wrote:

Brilliant! Just what I was looking for! And I didn't even have to ask the questions. Thanks.

Philip

Gary''s Student wrote:
Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub

How do I generate a list of the tabs/worksheets from a workboo
 

No links are created.......just a list of the sheets is returned.

Perhaps you should start a new thread with a better description of what you need
doing?


Gord Dibben MS Excel MVP

On Wed, 23 May 2007 06:06:01 -0700, graloe
wrote:

This sounds very similar to what I need to do... but how did you create the
links to each sheet?

"Gary''s Student" wrote:

Glad I could be of help.
--
Gary''s Student
gsnu200709


"Philip" wrote:

Brilliant! Just what I was looking for! And I didn't even have to ask the questions. Thanks.

Philip

Gary''s Student wrote:
Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub

How do I generate a list of the tabs/worksheets from a workboo
 

Yes... thank you so much for responding!

"Gord Dibben" wrote:

No links are created.......just a list of the sheets is returned.

Perhaps you should start a new thread with a better description of what you need
doing?


Gord Dibben MS Excel MVP

On Wed, 23 May 2007 06:06:01 -0700, graloe
wrote:

This sounds very similar to what I need to do... but how did you create the
links to each sheet?

"Gary''s Student" wrote:

Glad I could be of help.
--
Gary''s Student
gsnu200709


"Philip" wrote:

Brilliant! Just what I was looking for! And I didn't even have to ask the questions. Thanks.

Philip

Gary''s Student wrote:
Here is part of your answer. Enter the following macro, then select some
cell and run the macro. It will produce the list of worksheets at the point
you selected:

Sub sheet_lister()
i = 0
For Each w In Worksheets
Selection.Offset(i, 0).Value = w.Name
i = i + 1
Next
End Sub