name of worksheet

  • Thread starter Thread starter bluebanker
  • Start date Start date
B

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?
 
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

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