Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
I have a workbook that has upwards of 50 worksheets. Is there a simple way
of generating a list of those worksheets names? I don't fancy having to do it manually? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
you don't say where you want to list them, this will display them in the
immediate window in the vb editor, do a control-G. if you want them in a specific place, post back Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next End Sub -- Gary "mepetey" wrote in message ... I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I don't fancy having to do it manually? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
Thanks for the fast response. I would like to insert them as a list in a
separate worksheet, and use as a validation list. .. "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... you don't say where you want to list them, this will display them in the immediate window in the vb editor, do a control-G. if you want them in a specific place, post back Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next End Sub -- Gary "mepetey" wrote in message ... I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I don't fancy having to do it manually? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
try this
for i=1 to worksheets.count cells(i,"a").value=sheets(i).name next i -- Don Guillett Microsoft MVP Excel SalesAid Software "mepetey" wrote in message ... I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I don't fancy having to do it manually? TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
then you can use something like one of these:
Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Worksheets("Sheet1").Range("A" & ws.Index) = ws.Name Next End Sub or Sub list_names() Dim i As Long For i = 1 To Worksheets.Count Worksheets("sheet1").Range("A" & i).Value = Worksheets(i).Name Next End Sub -- Gary "mepetey" wrote in message ... Thanks for the fast response. I would like to insert them as a list in a separate worksheet, and use as a validation list. . "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... you don't say where you want to list them, this will display them in the immediate window in the vb editor, do a control-G. if you want them in a specific place, post back Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next End Sub -- Gary "mepetey" wrote in message ... I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I don't fancy having to do it manually? TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
I have a workbook that has upwards of 50 worksheets. Is there a simple way
of generating a list of those worksheets names? I don't fancy having to do it manually? This is something I use to generate a Table of Contents sheet in a workbook, which includes a hyperlink to each sheet: Sub TableOfContents() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Sheets If ws.Name = "Table of Contents" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next ws Set wsTOC = ActiveWorkbook.Worksheets.Add(Befo=ActiveWorkbo ok.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add _ Anchor:=wsTOC.Cells(r, 1), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Name wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next ws Columns("A:A").EntireColumn.AutoFit Cells.Font.Name = "Times New Roman" Range("A1").Select Application.CommandBars("Web").Visible = True Application.ScreenUpdating = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
list worksheets in a workbook.
Thanks to one and all for the help! much appreciated
"IanKR" wrote in message ... I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I don't fancy having to do it manually? This is something I use to generate a Table of Contents sheet in a workbook, which includes a hyperlink to each sheet: Sub TableOfContents() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Sheets If ws.Name = "Table of Contents" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next ws Set wsTOC = ActiveWorkbook.Worksheets.Add(Befo=ActiveWorkbo ok.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add _ Anchor:=wsTOC.Cells(r, 1), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Name wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next ws Columns("A:A").EntireColumn.AutoFit Cells.Font.Name = "Times New Roman" Range("A1").Select Application.CommandBars("Web").Visible = True Application.ScreenUpdating = True End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A bit more coding is all I need.
The listing of worksheets is 99% of what I needed. What I would like to add are the contents from the B2, C21, and C32 cells from each worksheet.
Is this possible using your original source code? CN IanKR wrote: This is something I use to generate a Table of Contents sheet in a workbook, 11-Nov-07 This is something I use to generate a Table of Contents sheet in a workbook, which includes a hyperlink to each sheet: Sub TableOfContents() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Sheets If ws.Name = "Table of Contents" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next ws Set wsTOC = ActiveWorkbook.Worksheets.Add(Befo=ActiveWorkbo ok.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add _ Anchor:=wsTOC.Cells(r, 1), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Name wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next ws Columns("A:A").EntireColumn.AutoFit Cells.Font.Name = "Times New Roman" Range("A1").Select Application.CommandBars("Web").Visible = True Application.ScreenUpdating = True End Sub Previous Posts In This Thread: On Saturday, November 10, 2007 3:46 PM mepetey wrote: list worksheets in a workbook. I have a workbook that has upwards of 50 worksheets. Is there a simple way of generating a list of those worksheets names? I do not fancy having to do it manually? TIA On Saturday, November 10, 2007 3:58 PM Gary Keramidas wrote: you don't say where you want to list them, this will display them in the you don't say where you want to list them, this will display them in the immediate window in the vb editor, do a control-G. if you want them in a specific place, post back Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next End Sub -- Gary "mepetey" wrote in message ... On Saturday, November 10, 2007 4:20 PM mepetey wrote: Thanks for the fast response. Thanks for the fast response. I would like to insert them as a list in a separate worksheet, and use as a validation list. .. On Saturday, November 10, 2007 4:22 PM Don Guillett wrote: try thisfor i=1 to worksheets.countcells(i,"a").value=sheets(i). try this for i=1 to worksheets.count cells(i,"a").value=sheets(i).name next i -- Don Guillett Microsoft MVP Excel SalesAid Software On Saturday, November 10, 2007 4:38 PM Gary Keramidas wrote: list worksheets in a workbook. then you can use something like one of these: Sub list_names() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Worksheets("Sheet1").Range("A" & ws.Index) = ws.Name Next End Sub or Sub list_names() Dim i As Long For i = 1 To Worksheets.Count Worksheets("sheet1").Range("A" & i).Value = Worksheets(i).Name Next End Sub -- Gary "mepetey" wrote in message ... On Sunday, November 11, 2007 7:40 AM IanKR wrote: This is something I use to generate a Table of Contents sheet in a workbook, This is something I use to generate a Table of Contents sheet in a workbook, which includes a hyperlink to each sheet: Sub TableOfContents() Dim ws As Worksheet, wsTOC As Worksheet Dim r As Long Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Sheets If ws.Name = "Table of Contents" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next ws Set wsTOC = ActiveWorkbook.Worksheets.Add(Befo=ActiveWorkbo ok.Sheets(1)) wsTOC.Name = "Table of Contents" wsTOC.Range("A1") = "Table of Contents" wsTOC.Range("A1").Font.Size = 18 r = 3 For Each ws In ActiveWorkbook.Worksheets If ws.Name < wsTOC.Name Then wsTOC.Hyperlinks.Add _ Anchor:=wsTOC.Cells(r, 1), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Name wsTOC.Cells(r, 1).Value = ws.Name r = r + 1 End If Next ws Columns("A:A").EntireColumn.AutoFit Cells.Font.Name = "Times New Roman" Range("A1").Select Application.CommandBars("Web").Visible = True Application.ScreenUpdating = True End Sub On Sunday, November 11, 2007 8:06 AM mepetey wrote: Thanks to one and all for the help! Thanks to one and all for the help! much appreciated Submitted via EggHeadCafe - Software Developer Portal of Choice Using the WebResource.axd Handler with Embedded ASP.NET Resources http://www.eggheadcafe.com/tutorials...sourceaxd.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a List From Worksheets in a WorkBook | Excel Worksheet Functions | |||
List All Worksheets in Workbook | Excel Discussion (Misc queries) | |||
Can I print a list of all of the worksheets within a workbook? | Excel Discussion (Misc queries) | |||
Can I see a list of the worksheets within a workbook without scro. | Excel Worksheet Functions | |||
List the worksheets in a workbook | Excel Programming |