Listing All Worksheets

[TimSwift]

I expect this is really obvious, but I cannot find the answer.

I have a Excel file with a lot (30+) of worksheets in it. I want to
produce a list of all the worksheets, either onto a blank worksheet or
as a print out.

Is there any easy way of doing this?

Many thanks

Tim Swift

 

[Bob Phillips]

TGim,

This will dump the names in the active worksheet.

Dim iRow As Long
Dim sh As Worksheet

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


--

HTH

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

 

[Norman Harker]

Hi Tim!

Here's one fairly basic approach:

Sub ListWorksheets()
Dim n As Integer
For n = 1 To Worksheets.Count
ActiveCell(n, 1) = Worksheets(n).Name
Next
End Sub

But are you aware that a right click on the arrows to the left of
sheet tabs will give you a clickable list?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Arvi Laanemets]

Hi

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.