print sheet tabs names

  • Thread starter Thread starter JDM
  • Start date Start date
J

JDM

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 **
 
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
 
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(ROW(A1)))
and copy the formula down as much as you do need.
 
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
 
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, 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 **
 
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 **




.
Click to expand...
 
Back
Top