automatically get worksheet name

  • Thread starter Thread starter johnzzhao
  • Start date Start date
J

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
 
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
 
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
 
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
 
Back
Top