Tab Names

A

Andy the Yeti

Hi,

Is it possible to have Excel populate a cell (for example A1) with the name
of that worksheet tab? This could then be used across the rest of the book.


Many thanks
 
S

smw226 via OfficeKB.com

Hi Andy,

There may be a much easier way todo this, but you can paste the below into a
module:

Public Function tab_name()
tab_name = ActiveSheet.Name
End Function

then in A1 enter = tab_name()

This will return the tab name into call A1 but if you make a change to the
tabs name, you will have to re-enter the formula as it won't autocalc.

Thanks,

Simon
 
A

Andy the Yeti

Thank guys both great!



smw226 via OfficeKB.com said:
Hi Andy,

There may be a much easier way todo this, but you can paste the below into a
module:

Public Function tab_name()
tab_name = ActiveSheet.Name
End Function

then in A1 enter = tab_name()

This will return the tab name into call A1 but if you make a change to the
tabs name, you will have to re-enter the formula as it won't autocalc.

Thanks,

Simon
Click to expand...
 
D

Dave Peterson

I think I'd use:

Option Explicit
Public Function tab_name()
tab_name = Application.Caller.Parent.Name
End Function

If I were using the UDF. Activesheet.name will be the name of the
activesheet--not always the sheet containing the formula.
 
Top