how to show aTAB name in a cell?

Microsoft Excel 2007
August 19, 2009 at 12:59:57
Specs: Windows XP

anyone can say how to:
- show a TAB name in a cell?


See More: how to show aTAB name in a cell?

Report •

August 19, 2009 at 16:47:37
Two ways:

1 - With a User Defined Function (UDF)

Put this code in a regular module of the VBA editor:

Function TabName()
  TabName = ActiveSheet.Name
End Function

In any cell put:


2 - By using the CELL() function

=CELL("filename",A1) will return the full path, including the Sheet Name, of the location of A1 in the sheet the function is used in. e.g.

C:\Documents and Settings\username\My Documents\[Book1.xls]Sheet1

Note: The file must be saved first for it to work.

Now you can use MID() to strip off everything except the sheet name:


Report •

August 21, 2009 at 18:46:55
hi DerbyDad03

=TABNAME() - doesn't work
=CELL("filename... - can't be used. i constantly save updated versions with naming xxx 1.00. changing it every time is too hard.

any other solutions?


Report •

August 21, 2009 at 19:22:30
Try using a Defined Name combined with DerbyDad03’s =MID function:

On the Task Bar

In the Define Name slot enter:


In the Refers to slot at the bottom enter:


Click OK

Select any cell you like and enter:


and the current Tab name should appear.


Report •

Related Solutions

August 21, 2009 at 19:57:23
re: =TABNAME() - doesn't work

It might help if you explained what doesn't work/what happens. It works fine for me when placed in a regular module within the VBA editor.

re: =CELL("filename... - can't be used

You do realize that I meant to use the word "filename" as written, quotes included, not the name of your file, don't you?

Using the argument "filename" with the CELL function picks up the whatever is the current name of the file as long as it has been saved at least once.

In other words, it's not:




which will return:

C:\Documents and Settings\username\My Documents\[Book1.xls]Sheet1

or whatever is the actual path to the file Book1.xls

See here for other CELL() arguments:

Report •

August 22, 2009 at 04:50:57
thanks for patience :-)

it's simple:
- my excel has columns with info.
- one sheet per client
- one sell above has client #, that belongs to the sheet.

normally I name tabname and the sell with client number, but it becomes too big to do it manually. so I would like to automate input in cell in sheet from tabname of same tab.

using function with A1 is not what I want, since I do not connect auto relationship yet, between main sheet and tabs

Report •

August 22, 2009 at 08:15:54
You've got me confused.

Can you give a before and after example.


Report •

August 22, 2009 at 08:29:33
I'm as confused as Mike is, but I'll mention this:

The A1 in the Cell function is only there so the CELL function as has a range address to work with. You could us any - absoultely any - cell reference in the function.


These will all return the same thing: the full path to Sheet, including the directory path, the workbook name and the sheet name.

The reference to a cell is only there so that the CELL function knows what to use to build the path.

Have you actually tried anything that I've suggested?

Report •

August 22, 2009 at 10:22:39
can i post a screenshot here?

Report •

August 22, 2009 at 10:30:02
No, but you can post a link.


Report •

Ask Question