Create a list of Sheet names

  • Thread starter Thread starter Al
  • Start date Start date
Al
This macro places all the sheet names in Column A of a sheet named
"ExistingSheet" starting in A1. HTH Otto
Sub ListShtNames()
Dim Dest As Range
Dim ws As Worksheet
With Sheets("ExistingSheet")
Set Dest = .Range("A1")
For Each ws In ThisWorkbook.Worksheets
Dest = ws.Name
Set Dest = Dest.Offset(1)
Next ws
End With
End Sub
 
What would be the vb code to create a list of the sheet names in an existing
sheet?
Click to expand...



This should list the sheet names, starting in cell A1 of the active
sheet and going down column A

Sub test()

Dim i As Integer

For i = 1 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Next i

End Sub



Good luck

Ken
Norfolk, Va
 
How about

Sub CreateSheetsList()

Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim sheetsCount As Long
Dim sheetNames() As Variant
Dim i As Long

Set wkbk = ActiveWorkbook
Set wksht = ActiveSheet

sheetsCount = wkbk.Sheets.Count
ReDim sheetNames(1 To sheetsCount)

For i = 1 To sheetsCount
sheetNames(i) = wkbk.Sheets(i).Name
Next i

wksht.Range(wksht.Range("A1"), wksht.Range("A" & sheetsCount)).Value =
Application.Transpose(sheetNames)

End Sub


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