Printing all worksheet names

R

Rory

Does anyone know if it possible to print a list of
worksheet names? A list similar to the one you would find
in the "Contents" tab of the files' Properties.

Thanks for any help you can provide.

Programme - XP Excel
 
A

Arvi Laanemets

Hi

This topic was covered lately in microsoft.excel.worksheet.functions NG
(thread 'print sheet tab names' started by JDB at 06.01.2004 16:00). Here is
the solution from me:

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

On some empty worksheet, enter the formula below into any cell, and copy it
down at least until all worksheets are listed:
=IF(ISERROR(TABBYINDEX(ROW(A1))),"",TABBYINDEX(ROW(A1)))
 
B

Bob Phillips

or directly

Sub PrintNAmes()
Dim sh As Worksheet
Dim i As Long

For Each sh In ActiveWorkbook.Worksheets
i = i + 1
Cells(i, 1).Value = sh.Name
Next sh

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top