Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One workbook to another . . . | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
create drop down list from different workbook | Excel Discussion (Misc queries) | |||
auto updating list | Excel Worksheet Functions | |||
Combo box list w/ data in 2nd workbook without opening both files | Excel Worksheet Functions |