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 Workshee
Dim r As Lon
Application.ScreenUpdating = Fals
For Each ws In ActiveWorkbook.Sheet
If ws.Name = "Table of Contents" The
Application.DisplayAlerts = Fals
ws.Delet
Application.DisplayAlerts = Tru
End I
Next w
Set wsTOC =
ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Sheets(1)
wsTOC.Name = "Table of Contents
wsTOC.Range("A1") = "Table of Contents
wsTOC.Range("A1").Font.Size = 1
r =
For Each ws In ActiveWorkbook.Worksheet
If ws.Name <> wsTOC.Name The
wsTOC.Hyperlinks.Add
Anchor:=wsTOC.Cells(r, 1),
Address:="",
SubAddress:="'" & ws.Name & "'!A1",
TextToDisplay:=ws.Name, ScreenTip:="Link to " & ws.Nam
wsTOC.Cells(r, 1).Value = ws.Nam
r = r +
End I
Next w
Columns("A:A").EntireColumn.AutoFi
Cells.Font.Name = "Times New Roman
Range("A1").Selec
Application.CommandBars("Web").Visible = Tru
Application.ScreenUpdating = Tru
End Sub
Previous Posts In This Thread:
list worksheets in a workbook.
I have a workbook that has upwards of 50 worksheets. Is there a simple wa
of generating a list of those worksheets names? I do not fancy having to d
it manually
TIA
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 windo
in the vb editor, do a control-G. if you want them in a specific place, post
bac
Sub list_names(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
Debug.Print ws.Nam
Nex
End Su
--
Gar
Thanks for the fast response.
Thanks for the fast response. I would like to insert them as a list in
separate worksheet, and use as a validation list
..
try thisfor i=1 to worksheets.countcells(i,"a").value=sheets(i).
try thi
for i=1 to worksheets.coun
cells(i,"a").value=sheets(i).nam
next
-
Don Guillet
Microsoft MVP Exce
SalesAid Softwar
(e-mail address removed)
Re: list worksheets in a workbook.
then you can use something like one of these
Sub list_names(
Dim ws As Workshee
For Each ws In ThisWorkbook.Worksheet
Worksheets("Sheet1").Range("A" & ws.Index) = ws.Nam
Nex
End Su
o
Sub list_names(
Dim i As Lon
For i = 1 To Worksheets.Coun
Worksheets("sheet1").Range("A" & i).Value = Worksheets(i).Nam
Nex
End Su
--
Gar
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 Workshee
Dim r As Lon
Application.ScreenUpdating = Fals
For Each ws In ActiveWorkbook.Sheet
If ws.Name = "Table of Contents" The
Application.DisplayAlerts = Fals
ws.Delet
Application.DisplayAlerts = Tru
End If
Next ws
Set wsTOC =
ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.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
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/tutorial...b6-f27c1f3beb34/using-the-webresourceaxd.aspx