automatically get worksheet name

[johnzzhao]

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

 

[Andy Pope]

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

 

[Mike H]

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

 

[Peter T]

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

 

[johnzzhao]

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