Need Formula for Getting Worksheet Name

[shade73]

Hey, I'm trying to insert the name of my worksheet into a cell. Does anyone know what formula that would be?

Also, if I wanted to pull from multiple worksheets
(say Sheet1 & Sheet2) and put it in Sheet3.. what is the formula to do that??

Thanks all!

 

[Greg Truby]

=REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1)),"")

That's the formula I was trying to find! Thanks, Aladin! (Now, shouldn't you be in bed ^zzzzz, it's gotta be like midnight over there...)

 

[Aladin Akyurek]

=REPLACE(CELL("filename",A1),1,SEARCH("]",CELL("filename",A1)),"")

That's the formula I was trying to find! Thanks, Aladin! (Now, shouldn't you be in bed ^zzzzz, it's gotta be like midnight over there...)

It looks like it's almost midnight. My roster says the class I have to give is at 1240 tomorrow. BTW, the REPLACE formula is the shortest native idiom available. If you don't have morefunc, that is.

 

[MartinK]

A nice one.

Thanks Aladin!

M.

 

[shade73]

wow, didn't mean to spark such a debate! lol. At any rate both of those formula's work great.

Thanks!

 

[Greg Truby]

Shade73,

Just in case you didn't come across it. Another poster asked the same question this morning. Barry answered by xref'ing back to this thread but then another member asked about retrieving filename and/or path. So you might find it interesting. Here's the address -- http://www.mrexcel.com/board2/viewtopic.php?t=179404

 

[Joe Was]

This is a custom sheet function.

It works both in a sheet or in a sub.

Sheet syntax:

=shName()

which returns the name of the active sheet.

Function shName(Optional CellRef As Range) As String
'Get active sheet name function!

Application.Volatile
On Error GoTo myErr
If CellRef Is Nothing Then
'User did not include a cell Refrence!
shName = Application.Caller.Parent.Name
Else
'Use the cell reference added to the function by the user!
shName = CellRef.Parent.Name
End If
GoTo myEnd

myErr:
MsgBox "You may use this Function, on a" & vbCr & _
"WorkSheet with or without a" & vbCr & _
"Cell Reference Parameter." & vbCr & vbCr & _
"Like: =shName(A1) or =shName()" & vbCr & vbCr & _
"To use this Function in a Sub as code," & vbCr & _
"you must add a Cell Reference as a" & vbCr & _
"Parameter!" & vbCr & vbCr & _
"Like: shName([A1])" & vbCr & _
"Which returns the currently active" & vbCr & _
"Sheet Name."

myEnd:
End Function