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?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

"bluebanker" wrote in message
...
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?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Take a look at David McRitchie's BuildTOC macro:

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

In article ,
bluebanker wrote:

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?

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


--
HTH
Nick Hodge
Southampton, England



"bluebanker" wrote in message
...
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?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/