name of worksheet

[bluebanker]

Is there a VBA function to display on the first worksheet of the
workbook all the names of the worksheets the book contains and puts
them in seperate rows?

 

[Don Guillett]

One way. Execute from the sheet where you want it.

Sub listsheets()
x = 1
For Each Sh In Sheets
Cells(x, 1).Value = Sh.Name
x = x + 1
Next
End Sub

 

[J.E. McGimpsey]

Take a look at David McRitchie's BuildTOC macro:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Nick Hodge]

Bluebanker

he code below will work. Place it in your personal.xls to run with any
open workbook

Sub showSheetNames()
Dim sht As Object
Dim shtNew As Worksheet
Dim x As Integer

On Error Resume Next

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

Set shtNew = ActiveWorkbook.Worksheets.Add
shtNew.Name = "Sheet List"
With shtNew.Range("A1:B1")
.Value = Array("Sheet Name", "Type")
.Font.Bold = True
End With

x = 2


For Each sht In ActiveWorkbook.Sheets
If sht.Name <> shtNew.Name Then
shtNew.Range("A" & x & ":B" & x).Value = Array(sht.Name,
shtTypeName(sht.Type))
x = x + 1
End If
Next sht

With shtNew
.Columns.AutoFit
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub