automatically get worksheet name
 

I am new to excel programming, and hope someone can help me out here.

I have a large excel workbook with many worksheet, I wish to use this
function and put worksheet name on the top of each sheet. The
function I have is RIGHT(CELL("filename")|LEN(CELL("filename"))-
FIND("]"|CELL("filename"))).
The problem is it doesn't automatically refresh when I move from sheet
to sheet.

Thank in advance,

John

automatically get worksheet name
 

Hi,

You need to add a reference to the CELL function in order to get each
individual sheet.

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
wrote in message
...
I am new to excel programming, and hope someone can help me out here.

I have a large excel workbook with many worksheet, I wish to use this
function and put worksheet name on the top of each sheet. The
function I have is RIGHT(CELL("filename")|LEN(CELL("filename"))-
FIND("]"|CELL("filename"))).
The problem is it doesn't automatically refresh when I move from sheet
to sheet.

Thank in advance,

John

automatically get worksheet name
 

Hi,

ALT+F11 to open VB editor. Double click 'This Workbook' and paste this in on
the right. Change the cell reference to where you want the name

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = ActiveSheet.Name
End Sub

Mike

" wrote:

I am new to excel programming, and hope someone can help me out here.

I have a large excel workbook with many worksheet, I wish to use this
function and put worksheet name on the top of each sheet. The
function I have is RIGHT(CELL("filename")|LEN(CELL("filename"))-
FIND("]"|CELL("filename"))).
The problem is it doesn't automatically refresh when I move from sheet
to sheet.

Thank in advance,

John

automatically get worksheet name
 

Andy has given you the answer but FWIW this is a bit shorter

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)

31 is the max length of a sheet name (at least in xl2003 and below)

Regards,
Peter T

wrote in message
...
I am new to excel programming, and hope someone can help me out here.

I have a large excel workbook with many worksheet, I wish to use this
function and put worksheet name on the top of each sheet. The
function I have is RIGHT(CELL("filename")|LEN(CELL("filename"))-
FIND("]"|CELL("filename"))).
The problem is it doesn't automatically refresh when I move from sheet
to sheet.

Thank in advance,

John

automatically get worksheet name
 

Thanks, it worked. I have to change "," to "|", using Excel 2007