List of worksheets

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

Is it possible to obtain a list of the worksheets in a workbook and, say,
store it in a separate sheet or export it to Word?

Thanks in advance.
 
Thanks. Do you happen to know if this can be done by, say, macro as I didn't
really want to buy a whole utility for such a straightforward task!
 
One way:

Public Sub ListSheets()
Dim i As Long
With Sheets.Add(After:=Sheets(Sheets.Count))
.Name = "Sheets in " & ThisWorkbook.Name
For i = 1 To Sheets.Count - 1
.Cells(i, 1).Value = Worksheets(i).Name
Next i
End With
End Sub

remove the -1 if you want to include the new sheet.
 
Many thanks. Unfortunately I got a runtime error. As I don't know any vba I
can't correct it myself!
 
Try cutting and pasting this - it works fine for me. Make sure it's
in a regular code module (Insert/Module in the VBE), not the
ThisWorkbook or a Worksheet code module.

The one real change I made was to limit the worksheet name to 31
characters in case your workbook name was longer.

Public Sub ListSheets()
Dim i As Long
With Worksheets.Add(After:=Sheets(Sheets.Count))
.Name = Left("Sheets in " & ThisWorkbook.Name, 31)
For i = 1 To Sheets.Count - 1
.Cells(i, 1).Value = Worksheets(i).Name
Next i
End With
End Sub
 
Excellent. Worked great with those instructions!

J.E. McGimpsey said:
Try cutting and pasting this - it works fine for me. Make sure it's
in a regular code module (Insert/Module in the VBE), not the
ThisWorkbook or a Worksheet code module.

The one real change I made was to limit the worksheet name to 31
characters in case your workbook name was longer.

Public Sub ListSheets()
Dim i As Long
With Worksheets.Add(After:=Sheets(Sheets.Count))
.Name = Left("Sheets in " & ThisWorkbook.Name, 31)
For i = 1 To Sheets.Count - 1
.Cells(i, 1).Value = Worksheets(i).Name
Next i
End With
End Sub
Click to expand...
 
Back
Top