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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
<(E-Mail Removed)> wrote in message
news:f0dc611c-48e4-4bce-aa4f-(E-Mail Removed)...
>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,

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

"(E-Mail Removed)" 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
>

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

<(E-Mail Removed)> wrote in message
news:f0dc611c-48e4-4bce-aa4f-(E-Mail Removed)...
>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

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