Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically get worksheet name
Thanks, it worked. I have to change "," to "|", using Excel 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
Automatically Populating a Worksheet | Excel Worksheet Functions | |||
automatically change worksheet tab name | Excel Worksheet Functions | |||
Automatically number a worksheet | Excel Discussion (Misc queries) | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions |