Putting worksheet name into cell on that sheet

[shades]

I remember seeing a response recently about a formula that takes the worksheet "name" and then inserts that into a cell on that worksheet.


[Nuts! I tried to search, but the search function isn't working right now, and I have searched 25+ pages and can't find what I need.]

 

[Ivan F Moala]

On 2002-04-12 12:49, shades wrote:
I remember seeing a response recently about a formula that takes the worksheet "name" and then inserts that into a cell on that worksheet.


[Nuts! I tried to search, but the search function isn't working right now, and I have searched 25+ pages and can't find what I need.]

Try =MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,255)

 

[Tim Francis-Wright]

http://www.mrexcel.com/board/viewtopic.php?topic=3816&forum=2

Methinks this hast what thou needest.

 

[cornbread]

try this


Sub sheet_name()
Range("a1") = ActiveSheet.Name
End Sub

 

[shades]

Thanks, Ivan, that's the approach I'm looking for.

However, that only works once in the entire workbook.

I have seven tabs: Q1-01, Q2-01, Q3-01 Q4-01, Q1-Q2, Q1-Q3, Q1-Q4.

Once I pasted in your formula into cell Q1-01!J3, it did what I wanted ("Q1-01" was in the cell). But when I pasted the formula into another sheet (Q2-01!J3), it took the name of that one ("Q2-01"), and then when I went back to worksheet Q1-01, that cell (Q1-01!J3) had changed to Q2-01.

 

[Jay Petrulis]

Hi,

If you want a worksheet function which is easier to use than the =MID(CELL(....

try the following UDF:

Public Function NameSheet()
NameSheet = Application.Caller.Parent.Name
End Function

Called as =NAMESHEET()

 

[Ricky Morris]

if you use Ivan's approach you should add a cell reference (a1) after "filename" to the formula to assure you get name of the active sheet without the need to recalc:

=MID(CELL("filename",a1),FIND("]",CELL("filename",a1),1)+1,255)

 

[shades]

Thanks, everyone!

Ricky, that little extra made the difference. And all of you made my day!!!

 

[Ivan F Moala]

On 2002-04-12 13:14, Ricky Morris wrote:
if you use Ivan's approach you should add a cell reference (a1) after "filename" to the formula to assure you get name of the active sheet without the need to recalc:

=MID(CELL("filename",a1),FIND("]",CELL("filename",a1),1)+1,255)

Good one Ricky...I'd forgotten about that for
Multisheet use.