Insert Tab name into worksheet?

scewing

New Member
Joined
Nov 4, 2002
Messages
3
Other than using headers and footers, is there a way to insert the name of a tab into a cell on a worksheet? I know you can insert the filename into a cell using the CELL function, but I can't seem to find a way to insert the tab name into a cell. Thanks in advance!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
[EDITED]

if you install the morefunc add-in (search this site for download info) you can use one of its functions called SHEETNAME(), which will allow you to do exactly what you are trying to do - lots of other useful functions also

you can download the morefunc addin for free at:
http://longre.free.fr/english/index.html

hth
kevin
This message was edited by kskinne on 2002-12-18 16:00
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

will strip the tab name (sheet name) out of the full path, if you have an IT dept like mine who frown upon downloading things which make your life much easier

it basically deducts the position of the "]" in the address from the overall length of the address - leaving the amount of characters remaining (which is the sheet name) then applies this number to the RIGHT function of the address, ie counting right to left

HTH
Chris
 

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
Our IT dept not only frowns on Add Ins but positively growls at me for even suggesting it so I use this little function. To copy worksheet name into a cell in cell on worksheet enter =WorkSheetName()

Function WorkSheetName() As String
ActiveSheet.Activate
WorkSheetName = ActiveSheet.Name
End Function
 

stevecook3dw

New Member
Joined
Feb 1, 2010
Messages
1

ADVERTISEMENT

I'm not familiar with the VB side of Excel. I tried pasting the short code in VB to make the FUNCTION, saved and returned to Excel. didn't work.

Using Office 2007 and Windows 7.

Any help would be appreciated. In the mean time I used the code someone else offered to strip out the worksheet name.
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
Exactly what I was looking for except; I also want it to list and link to all of the sheets in the workbook. Is there any way to automate this? My workbook has 45 worksheets and grows weekly
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

You can use Zack Barresse's Table of Contents generator:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CreateTOC()<br>    <SPAN style="color:#007F00">'   Code by Zack Baresse</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <br>        <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>            ct <SPAN style="color:#00007F">As</SPAN> Chart, _<br>            shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>            nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        nrow = 3<br>        i = 1<br>        numCharts = ActiveWorkbook.Charts.Count<br>        <br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br>        Sheets("Table of Contents").Activate<br>        <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _<br>        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>hasSheet:<br>    Sheets.Add Before:=Sheets(1)<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasNew<br><br>createNew:<br>    Sheets("Table of Contents").Delete<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br><br>hasNew:<br>    tmpCount = ActiveWorkbook.Charts.Count<br>    <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1<br>        ActiveSheet.Name = "Table of Contents"<br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#007F00">'.Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> .Range("B2")<br>                    .Value = "Table of Contents"<br>                    .Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>                    .Font.Name = "Calibri"<br>                    .Font.Size = "24"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            nrow = nrow + 1<br>            <SPAN style="color:#00007F">With</SPAN> ws<br>                shtName = ws.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Hyperlinks.Add _<br>                        Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _<br>                        shtName & "'!A1", TextToDisplay:=shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <br>        <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts<br>                nrow = nrow + 1<br>                shtName = ct.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Value = shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> ct<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")<br>                .MergeCells = <SPAN style="color:#00007F">True</SPAN><br>                .HorizontalAlignment = xlLeft<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>            <SPAN style="color:#00007F">With</SPAN> .Range("C:C")<br>                .EntireColumn.AutoFit<br>                .Activate<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Range("B4").Select<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _<br>        "Charts are listed after regular " & vbCrLf & _<br>        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,
 

Forum statistics

Threads
1,143,835
Messages
5,721,060
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top