Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet names
Hi all, Is there a simple way to list the worksheet names into cells? Thank you. JF |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copyright and worksheet protection | Excel Discussion (Misc queries) | |||
Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
Worksheet name and Backward compatibility | Excel Discussion (Misc queries) |