Worksheet Tab Name As a Cell Value

brucer7 · Apr 21st, 2002, 05:59 PM

Is there a formula for getting the worksheet tab name as a cell value ??? Or would a macro be the only way ???

Thanks for the help !!!

Alleleuia,
Bruce Radic

RET79 · Apr 21st, 2002, 06:04 PM

Best I can come up with here is to type in something like

Range("A1") = activesheet.name

in the immediate window.

Hope this helps

RET79

brettvba · Apr 21st, 2002, 06:33 PM

or
Sub cellval()
Range("a1").Value = ActiveSheet.Name
End Sub
will do it
I couldn't get the first example above to work?

[ This Message was edited by: brettvba on 2002-04-21 17:35 ]

Jay Petrulis · Apr 21st, 2002, 07:26 PM

Hi,

Standard way using a cell formula
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

The workbook must have been saved for this approach to work.

Alternatively, you can write simple UDF:
-----------------------
Function NameSheet()
NameSheet = Application.Caller.Parent.Name
End Function
------------------------

Called as =NameSheet()

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-04-21 18:27 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 21st, 2002, 05:59 PM	#1
brucer7 New Member Join Date: Apr 2002 Posts: 3	Is there a formula for getting the worksheet tab name as a cell value ??? Or would a macro be the only way ??? Thanks for the help !!! Alleleuia, Bruce Radic

Apr 21st, 2002, 06:04 PM	#2
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Best I can come up with here is to type in something like Range("A1") = activesheet.name in the immediate window. Hope this helps RET79

Apr 21st, 2002, 06:33 PM	#3
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	or Sub cellval() Range("a1").Value = ActiveSheet.Name End Sub will do it I couldn't get the first example above to work? [ This Message was edited by: brettvba on 2002-04-21 17:35 ]

Apr 21st, 2002, 07:26 PM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Standard way using a cell formula =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)) The workbook must have been saved for this approach to work. Alternatively, you can write simple UDF: ----------------------- Function NameSheet() NameSheet = Application.Caller.Parent.Name End Function ------------------------ Called as =NameSheet() Bye, Jay [ This Message was edited by: Jay Petrulis on 2002-04-21 18:27 ]