worksheet name

[imillar]

i have multiple worksheets in a file, and would like to know if i can list the worksheet names on another worksheet for a summary.

 

[dk]

Hi,

How about this?

Sub ListSheets()
Dim shtEnum As Object, shtDest As Worksheet, lngRow As Long

Set shtDest = Worksheets.Add
On Error Resume Next
shtDest.Name = "Sheet List"
shtDest.Range("A1").Value = "Sheet name"

lngRow = 2
For Each shtEnum In ActiveWorkbook.Sheets
    If shtEnum.Name <> "Sheet List" Then
        shtDest.Cells(lngRow, 1).Value = shtEnum.Name
        lngRow = lngRow + 1
    End If
Next shtEnum

End Sub

HTH,
Dan

 

[Dave Hawley]

Hi


Run this code on the sheet you want you names in:

Sub AllSheet()
Dim i As Integer

    For i = 1 To ActiveWorkbook.Sheets.Count
        Cells(i, 1) = Sheets(i).Name
    Next i
 
End Sub