Printing all worksheet names

  • Thread starter Thread starter Rory
  • Start date Start date
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
 
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)))
 
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)
 
Perhaps this feature could be added to the next release of Excel. Certainly the Accounting community would love to easily print a list of all the tab names within some of their Excel workbooks

Cheers
Ann
The Norwich Group
 
This macro from a recent post by Gord Dibben looks just the job ..
so there's no need to wait <g>

-------------------------
From: Gord Dibben (gorddibbATshawDOTca)
Subject: Re: sheet tab names
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.excel
Date: 2004-03-10 16:55:48 PST

Dave

Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub

Gord Dibben Excel MVP
Is there a quick way to list all sheet tab names into a
given sheet column for quick reference purposes?

tia,
Dave
Click to expand...
----------------------------
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Anne said:
Perhaps this feature could be added to the next release of Excel.
Click to expand...
Certainly the Accounting community would love to easily print a list of all
the tab names within some of their Excel workbooks.
 
Back
Top