Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
Is there an automated way to create an Index worksheet for a large workbook?
I inherited a bunch of workbooks with a different product on each worksheet. The only place the part number is listed is on the tab. I'm part way thru manually creating a worksheet that lists the tab name and links to the individual worksheets. It's much too time consuming. Any ideas will be appreciated. Thanks! -- Smitty Somerset, PA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
It is quite easy with the small macro:
Sub ListSheets() 'list of sheet names starting at A1 Dim rng As Range Dim i As Integer Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Regards, Ryan-- -- RyGuy "Bill Smith" wrote: Is there an automated way to create an Index worksheet for a large workbook? I inherited a bunch of workbooks with a different product on each worksheet. The only place the part number is listed is on the tab. I'm part way thru manually creating a worksheet that lists the tab name and links to the individual worksheets. It's much too time consuming. Any ideas will be appreciated. Thanks! -- Smitty Somerset, PA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
See here
http://www.mvps.org/dmcritchie/excel/buildtoc.htm Vaya con Dios, Chuck, CABGx3 "Bill Smith" wrote: Is there an automated way to create an Index worksheet for a large workbook? I inherited a bunch of workbooks with a different product on each worksheet. The only place the part number is listed is on the tab. I'm part way thru manually creating a worksheet that lists the tab name and links to the individual worksheets. It's much too time consuming. Any ideas will be appreciated. Thanks! -- Smitty Somerset, PA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
"ryguy7272" wrote in message
... It is quite easy with the small macro: Sub ListSheets() 'list of sheet names starting at A1 Dim rng As Range Dim i As Integer Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Regards, Ryan-- -- RyGuy Thanks Ryan. It worked great. Is there anyway to also create the hyperlinks from the resulting sheet names to each tab (worksheet)? -- Smitty Somerset, PA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
Thanks Chuck!
-- Smitty Somerset, PA "CLR" wrote in message ... See here http://www.mvps.org/dmcritchie/excel/buildtoc.htm Vaya con Dios, Chuck, CABGx3 "Bill Smith" wrote: Is there an automated way to create an Index worksheet for a large workbook? I inherited a bunch of workbooks with a different product on each worksheet. The only place the part number is listed is on the tab. I'm part way thru manually creating a worksheet that lists the tab name and links to the individual worksheets. It's much too time consuming. Any ideas will be appreciated. Thanks! -- Smitty Somerset, PA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
You're welcome.........I got that from an old Frank Kable post..........
Vaya con Dios, Chuck, CABGx3 "Bill Smith" wrote: Thanks Chuck! -- Smitty Somerset, PA "CLR" wrote in message ... See here http://www.mvps.org/dmcritchie/excel/buildtoc.htm Vaya con Dios, Chuck, CABGx3 "Bill Smith" wrote: Is there an automated way to create an Index worksheet for a large workbook? I inherited a bunch of workbooks with a different product on each worksheet. The only place the part number is listed is on the tab. I'm part way thru manually creating a worksheet that lists the tab name and links to the individual worksheets. It's much too time consuming. Any ideas will be appreciated. Thanks! -- Smitty Somerset, PA |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating an Index
Bill
Rather than an index sheet with hyperlinks.......how do you get back to the index sheet?.............try a sheet navigation toolbar or similar. Sheet navigation bar from Debra Dalgleish's site. http://www.contextures.on.ca/xlToolbar01.html Or Bob Phillips' Browsesheets macro which I prefer. See this google search result. All one line for the URL http://groups.google.com/group/micro...c19464f875bd25 Gord Dibben MS Excel MVP On Wed, 2 Jan 2008 14:44:59 -0500, "Bill Smith" wrote: Thanks Chuck! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart axes color index vs font color index | Charts and Charting in Excel | |||
How do I pull the col. index value as well as row index value | Excel Discussion (Misc queries) | |||
Creating and Index with worksheet names | Excel Discussion (Misc queries) | |||
INDEX | Excel Worksheet Functions | |||
Index needs a little help | Excel Worksheet Functions |