Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Name: amoralis
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 FunctionIn any cell put:
=TabName()
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:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
0 
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?
thnx
0
Try using a Defined Name combined with DerbyDad03’s =MID function:
On the Task Bar
Insert
Name
DefineIn the Define Name slot enter:
Tabname
In the Refers to slot at the bottom enter:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Click OK
Select any cell you like and enter:=Tabname
and the current Tab name should appear.
0
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:
=CELL("Book1.xls",A1)
it's
=CELL("filename",A1)
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:
0
hi
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.
P.S.
using function with A1 is not what I want, since I do not connect auto relationship yet, between main sheet and tabs
0
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.
=CELL("filename",A1)
=CELL("filename",Z10)
=CELL("filename",AR78)
=CELL("filename",IV65535)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?
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
