Worksheet names
 

Hi all,

Is there a simple way to list the worksheet names into
cells?

Thank you.
JF

One way:

Sub ListWSNames()
Dim sh As Worksheet
Dim i As Long

Application.ScreenUpdating = False

With ActiveWorkbook
.Worksheets(1).Select
Set sh = .Worksheets.Add
End With

With sh
For i = 2 To ActiveWorkbook.Worksheets.Count
.Cells(i, "A").Value = Worksheets(i).Name
Next i
.Cells(1, "A").Value = "Sheet Names (excl. this one)"
End With

Range("A:A").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

---
Press ALT+F11, Insert Module, and paste in the code
above. It creates a new worksheet and creates the list
there.

HTH
Jason
Atlanta, GA

-----Original Message-----

Hi all,

Is there a simple way to list the worksheet names into
cells?

Thank you.
JF
.

Hi

You can use an UDF. The one below returns tab name from tab's index (order
number)

---------
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---------

P.e.
=TABBYINDEX(3) returns the name of 3th sheet in your workbook


Arvi Laanemets


"JF Bouthillier" wrote in message
...

Hi all,

Is there a simple way to list the worksheet names into
cells?

Thank you.
JF

"JF Bouthillier" wrote...
Is there a simple way to list the worksheet names into
cells?

Since Excel can paste the definitions of all defined names into cells, it's
odd & unfortunate there's no menu command to do the same for worksheet
names.

Yet another approach using VBA - a udf that returns an array of worksheet
names in the file containing the cell formula calling the udf or the file
containing its optional range reference argument.


Function slst(Optional t As String = "CMS", Optional r As Range) As Variant
'----------------------------------------------------------------
'optional 1st arg specifies which sheets to include in results
'using last char of XL4 worksheet extensions: xlC - charts,
'xlM - macros, xlS - [work]sheets -- all other chars ignored
'optional 2nd arg used to specify which *OPEN* workbook's sheets
'1st defaults to all sheets, latter defaults to workbook which
'contains the calling formula.
'----------------------------------------------------------------
Const C As Long = 1, M As Long = 2, S As Long = 3

Dim rv As Variant, tt(1 To 3) As Boolean
Dim sc As Sheets, x As Variant, n As Long

If r Is Nothing Then

If TypeOf Application.Caller Is Range Then
Set r = Application.Caller
Else
Set r = ActiveCell
End If

End If

Set sc = r.Parent.Parent.Sheets

If InStr(1, t, "C", vbTextCompare) 0 Then tt(C) = True
If InStr(1, t, "M", vbTextCompare) 0 Then tt(M) = True
If InStr(1, t, "S", vbTextCompare) 0 Then tt(S) = True

ReDim rv(1 To sc.Count)

For Each x In sc
If (TypeOf x Is Chart And tt(C)) _
Or ((x.Type = xlExcel4MacroSheet _
Or x.Type = xlExcel4IntlMacroSheet) And tt(M)) _
Or (x.Type = xlWorksheet And tt(S)) Then
n = n + 1
rv(n) = x.Name
End If
Next x

ReDim Preserve rv(1 To n)

slst = Application.WorksheetFunction.Transpose(rv)

End Function


Best not to make this volatile.