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