Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I copy the worksheet tab name to a cell?
I've got a workbook with 12 tabs. Within one of the tabs, I'd like to
create a table of the 12 tab names. When I change a tab name, I want the table to be updated automatically. |
#2
|
|||
|
|||
I posted an example file to http://www.Galimi.com/Examples/shtChange.xls
Following is the Workbook_Activate code Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < shtPlace.Name Then Exit Sub For Each sht In ThisWorkbook.Sheets shtPlace.Range("nmSht").Offset(intr) = sht.Name intr = intr + 1 Next End Sub http://HelpExcel.com "shoshaw" wrote: I've got a workbook with 12 tabs. Within one of the tabs, I'd like to create a table of the 12 tab names. When I change a tab name, I want the table to be updated automatically. |
#3
|
|||
|
|||
Assuming that your tabs are named Sheet1, etc, then in A1 put
=MID(CELL("Filename",Sheet1!A1),FIND("]",CELL("Filename",Sheet1!A1))+1,255) In A2 =MID(CELL("Filename",Sheet1!A2),FIND("]",CELL("Filename",Sheet1!A2))+1,255) etc As you change any sheet names the table uipdates. Just change to your sheet names. If they include spaces in the names, enclose the sheet name in single quotes. -- HTH RP (remove nothere from the email address if mailing direct) "galimi" wrote in message ... I posted an example file to http://www.Galimi.com/Examples/shtChange.xls Following is the Workbook_Activate code Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name < shtPlace.Name Then Exit Sub For Each sht In ThisWorkbook.Sheets shtPlace.Range("nmSht").Offset(intr) = sht.Name intr = intr + 1 Next End Sub http://HelpExcel.com "shoshaw" wrote: I've got a workbook with 12 tabs. Within one of the tabs, I'd like to create a table of the 12 tab names. When I change a tab name, I want the table to be updated automatically. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i copy formula and change worksheet instead of cell | Excel Worksheet Functions | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
sending data from one cell to another (not using copy & paste) | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |