Listing All Worksheets

  • Thread starter Thread starter TimSwift
  • Start date Start date
T

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
 
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)
 
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.
 
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.
 
Back
Top