# Thread: Tab name (2003)

1. ## Tab name (2003)

Good morning
I'm creating this summary of a workbook with about 80 sheets or so.
One of the fields will be the name of the worksheet tab. Is there a quick way of copying this ??? All the rest of the information will be by linking, and rather then making a mistake typing in the worksheet tab manually, I'm hoping there is a fast and easy way

2. ## Re: Tab name (2003)

The formula below will return the name of the sheet.

<code>
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
</code>

If the workbook has not been saved, it will return #Value.

3. ## Re: Tab name (2003)

Thank you so much, that works great

4. ## Re: Tab name (2003)

Maybe I am unsure of what the user wanted to do. I thought it was to call the names from the tab of other sheets into a Summary sheet. I set up three sheets named Able, Baker, Charlie and a Summary sheet. Entering your formula on the Summary sheet in A1 returned the name Summary. Is the formula you suggested just to pick up the name of the current sheet or is it supposed to pick up the names of the subsequent sheets?

Thanks.

5. ## Re: Tab name (2003)

It picks up the name of the sheet that is referenced in the CELL function in the formula. To pick up the name of the Able sheetg, the formula would need to be changed to:

<code>
=MID(CELL("filename",Able!A1),FIND("]",CELL("filename",Able!A1))+1,20)
</code>

Since that formula contains the name of the sheet, it might not be obvious why this would be better than just putting the name of the sheet into the cell. However, the sheet name in the formula will be updated if the sheet's name is changed, and the cell will contain the correct name.

The other thing that could be done here would be to place the original formula in a cell on each sheet. Then reference that cell in the Summary sheet.

6. ## Re: Tab name (2003)

Thanks so much. Will have to study, but this is very useful for some summary work I am doing.

7. ## Re: Tab name (2003)

In your workbook create a new worksheet called Index.

Right hand click the new tab|View code

Paste in the following code

<div style="width: 100%; background-color: #FFFFFF;"><font color=black>
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1

With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
</font color=black>
</div hiblock>

Return to the workbook click another tab and now activate the Index worksheet....enjoy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

8. ## Re: Tab name (2003)

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

The free ASAP Excel Utilities Addin (http://www.asap-utilities.com) lets you create an index of all workbook sheets automatically, and lets you jump to it when clicking the relevant entry. In addition, it contains numerous other useful tools.

Regards,
Teunis

9. ## Re: Tab name (2003)

I read this post and found it quite intriguing. One question I didnt see in there, was If I set up a workbook that lists the other workbooks, using that formula, is there a way to hyperlink that to go to a particular worksheet. I tired the hyperlink in the cell but that didnt seem to work. Great Post and extremely useful

10. ## Re: Tab name (2003)

John,

The freeware ASAP Utilities lets you create an index sheet with a clickable list of all worksheets (tabs) within the present workbook. That's all. The shareware Spreadsheet Assistant (http://www.add-ins.com) goes one step further: it creates an index lists but also a clickable cell in each sheet referring to the index sheet, i.e. you can move quickly from list to the sheet and vice versa. Note that this is also limited to sheets within a single workbook.

Regards, Teunis

11. ## Re: Tab name (2003)

Hyperlinks are easy to make for any cell. If you already have the sheet names listed in some cells, do Ctrl+k to get to the hyperlink menu (or right-click on the cell and pick Hyperlink). In the new menu, look at the "Link to" fields on the left side, select "Place in this document". Now in "type the cell reference field" select the sheet and cell you want the link to go to from the choices in the text box. You can change the name shown for the link to anything you want (from the Text To Display field).

Deb

12. ## Re: Tab name (2003)

This works great.... now how would you add to it to take a cell from each page and show that as well on the index page you created. I have added this into a file which has about 100 worksheets. I would like to see the same Cell (B10) right next to the coresponding information on the index page.

13. ## Re: Tab name (2003)

Immediately above the line
<code>
End With
</code>
insert the following line:
<code>
Me.Range("B" & I) = .Range("B10")</code>

14. ## Re: Tab name (2003)

Hi Corey

This should do it

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
Dim j As Variant
l = 1

With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index

SubAddress:="Index", TextToDisplay:="Back to Index"
j = wSheet.Cells(10,2).Value
End With

SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
Me.Cells(l, 2).Value = j
End If
Next wSheet
End Sub

Added items in bold...I made j a variant as I was unsure what you wanted to add, but I guess it is text so change variant to string

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•