Getting the names of the tab sheets

  • Thread starter Thread starter Moises Munoz
  • Start date Start date
M

Moises Munoz

Is there a way to get the names of all the tab sheets
present in a workbook displayed in a column of a specific
sheet of the same worrkbook?

For example :

I have a workbook with 11 sheets, their names
are "one", "two", ......, "eleven".
In sheet "one", in cells A1 to A10, the
names "two", "three", ......"eleven" , should appear.

Thanks and regards,

Moises
 
Hi

=MID(CELL("filename",SheetName!A1),FIND("]",CELL("filename",SheetName!A1))+1
,255)
returns SheetName. The formula is adjusted automatically whenever you change
sheet name, but of course you have to edit formulas every time you add or
remove sheets from workbook.

Another solution is to create an UDF
---
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---
On worksheet where you want the list of sheet names, enter the formula below
into any cell, and copy it
down at least until all worksheets are listed:
=IF(ISERROR(TABBYINDEX(ROW(A1))),"",TABBYINDEX(ROW(A1)))
Now you have names of all worksheets listed in same order as seet tabs are
placed.
 
"Another solution is to create an UDF" <<< I Can't get this to work
do you mind reviewing and commenting as to what problems I might be
having?
TIA,



Arvi Laanemets said:
Hi

=MID(CELL("filename",SheetName!A1),FIND("]",CELL("filename",SheetName!A1))+1
,255)
returns SheetName. The formula is adjusted automatically whenever you change
sheet name, but of course you have to edit formulas every time you add or
remove sheets from workbook.

Another solution is to create an UDF
---
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---
On worksheet where you want the list of sheet names, enter the formula below
into any cell, and copy it
down at least until all worksheets are listed:
=IF(ISERROR(TABBYINDEX(ROW(A1))),"",TABBYINDEX(ROW(A1)))
Now you have names of all worksheets listed in same order as seet tabs are
placed.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Moises Munoz said:
Is there a way to get the names of all the tab sheets
present in a workbook displayed in a column of a specific
sheet of the same worrkbook?

For example :

I have a workbook with 11 sheets, their names
are "one", "two", ......, "eleven".
In sheet "one", in cells A1 to A10, the
names "two", "three", ......"eleven" , should appear.

Thanks and regards,

Moises
Click to expand...
Click to expand...
 
Arvi,

CANCEL my request for assistance... I Got it !!
I changed all references to TabByIndex to GTabIndex.
That got it going...
Tks,
JMay

Arvi Laanemets said:
Hi

=MID(CELL("filename",SheetName!A1),FIND("]",CELL("filename",SheetName!A1))+1
,255)
returns SheetName. The formula is adjusted automatically whenever you change
sheet name, but of course you have to edit formulas every time you add or
remove sheets from workbook.

Another solution is to create an UDF
---
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function
---
On worksheet where you want the list of sheet names, enter the formula below
into any cell, and copy it
down at least until all worksheets are listed:
=IF(ISERROR(TABBYINDEX(ROW(A1))),"",TABBYINDEX(ROW(A1)))
Now you have names of all worksheets listed in same order as seet tabs are
placed.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Moises Munoz said:
Is there a way to get the names of all the tab sheets
present in a workbook displayed in a column of a specific
sheet of the same worrkbook?

For example :

I have a workbook with 11 sheets, their names
are "one", "two", ......, "eleven".
In sheet "one", in cells A1 to A10, the
names "two", "three", ......"eleven" , should appear.

Thanks and regards,

Moises
Click to expand...
Click to expand...
 
Hi


JMay said:
"Another solution is to create an UDF" <<< I Can't get this to work
do you mind reviewing and commenting as to what problems I might be
having?
Click to expand...

Copy from my previous posting all starting with row
'Public Function ...'
and ending with row
'End Function'

Activate your workbook, and press Alt+F11 - VBA editor is opened.
Select from menu Insert.Module - an empty module is opened. Paste the copied
function into it, and close VBA editor.

Now enter the formula as described, copy it, etc.
PS. When you activate formula wizard (pressing Paste Function button) aftyer
that, you see the function TABBYINDEX() available in 'User defined' section.

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

Arvi Laanemets said:
Hi
Click to expand...
=MID(CELL("filename",SheetName!A1),FIND("]",CELL("filename",SheetName!A1))+1
Click to expand...
 
Back
Top