Here's an array function that will return the names of the worksheets
to a range of cells.

Function SheetNames() As Variant
Dim WB As Workbook
Dim WS As Worksheet
Dim SNames() As String
Dim Trans As Boolean
Dim L As Long
Dim N As Long
Dim R As Range

Set R = Application.Caller
Set WB = R.Parent.Parent
If R.Columns.Count = 1 Then
L = R.Rows.Count
Trans = True
Else
L = R.Columns.Count
Trans = False
End If
ReDim SNames(1 To L)

For N = 1 To Application.Min(L, WB.Worksheets.Count)
SNames(N) = WB.Worksheets(N).Name
Next N

If Trans Then
SheetNames = Application.Transpose(SNames)
Else
SheetNames = SNames
End If
End Function


Select the range of cells that are to receive the sheet names. Then,
type =SheetNames() and press CTRL SHIFT ENTER instead of just ENTER.
It will not work if you press ENTER rather than CTRL SHIFT ENTER. If
the range of cells is greater than the number of worksheets, the cells
at the end of the range will be empty. If the number of selected cells
is less than the number of worksheets, only the first N worksheet
names will be returned, where N is the number of selected cells. You
can enter the function into a range of cells that spans multiple rows
in a single column or in a range that spans several columns in a
single row.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Wed, 30 Sep 2009 09:53:02 -0700, Al
wrote:

What would be the vb code to create a list of the sheet names in an existing
sheet?