[mepetey]

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

[Gary Keramidas]

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

[mepetey]

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

[Don Guillett]

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

[Gary Keramidas]

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

[IanKR]

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

[mepetey]

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

[Chet Newman]

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