-----Original Message-----
...
...
''list of sheet names in a workbook - placed on a new worksheet
Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub
...
Why not make it a udf? That way it could modify itself if the OP ever changed
worksheet names.
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, 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
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 r.Parent.Parent.Sheets.Count)
For Each x In Application.Caller.Parent.Parent.Sheets
If (x.Type = -4169 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.
