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.

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.

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.