print sheet tabs names

Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.

JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
..Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.

Or this one

It will add a sheet to your workbook and add the sheetnames
Then print and delete the sheet.

Sub PrintTabNames()
Application.ScreenUpdating = False
Dim Nsheet As Worksheet
Set Nsheet = Sheets.Add
Dim WS As Worksheet
Dim r As Integer
r = 1
For Each WS In Worksheets
If WS.Name Nsheet.Name Then
Nsheet.Range("A" & r) = WS.Name
r = r + 1
End If
Next WS
Nsheet.PrintOut
Application.DisplayAlerts = False
Nsheet.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Paul B" wrote in message ...
JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
.Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.

Hi

Here is an another way (I copied here my response to thread 'Listing all
worksheets', started by TimSwift at 23.12.2003 15:36)

Create an UDF
---
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---

Add a sheet SheetList. When you want all your sheets to be listed started
from cell A2, then:
A2=IF(ISERROR(TABBYINDEX(ROW(A1)));"";TABBYINDEX(R OW(A1)))
and copy the formula down as much as you do need.

--
(When sending e-mail, use address )
Arvi Laanemets



"JDM" wrote in message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.

Paul B
Thanks for the reply, but when I run the macro listed, I
get a compile error at line "..Value = ws.Name" any
suggestions?
Thanks
-----Original Message-----
JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your
worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
..Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can
benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in
message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.


.

JDM,

There should be only a single period, not two, before the word
"Value".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JDM" wrote in message
...
Paul B
Thanks for the reply, but when I run the macro listed, I
get a compile error at line "..Value = ws.Name" any
suggestions?
Thanks
-----Original Message-----
JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your
worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
..Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can
benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in
message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.


.

JDM, the line should only have one . like this, .Value = ws.Name, not
...Value = ws.Name, copy and paste the code and see if it works

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in message
...
Paul B
Thanks for the reply, but when I run the macro listed, I
get a compile error at line "..Value = ws.Name" any
suggestions?
Thanks
-----Original Message-----
JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your
worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
..Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can
benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in
message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets and
would like to print a list of the sheet tabs.


.

That worked perfectly. Thanks for the help
-----Original Message-----
JDM, the line should only have one . like this, .Value =
ws.Name, not
...Value = ws.Name, copy and paste the code and see if it
works

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can
benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JDM" wrote in
message
...
Paul B
Thanks for the reply, but when I run the macro listed, I
get a compile error at line "..Value = ws.Name" any
suggestions?
Thanks
-----Original Message-----
JDM, here is one way

Sub List_All_Sheets()
'will list all worksheet names
'Click the first cell where you want to list your
worksheet names
'and run this macro
Dim i As Integer
Dim ws As Worksheet
i = 0
For Each ws In ActiveWorkbook.Worksheets
With ActiveCell.Offset(i, 0)
..Value = ws.Name
End With
i = i + 1
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others
can
benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email
**

"JDM" wrote in
message
...
Is there a way to print a list of the sheet tabs in
Excel. I have a spreadsheet with hundreds of sheets
and
would like to print a list of the sheet tabs.


.



.