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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
365
Platform
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,
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,626
Messages
5,512,477
Members
408,899
Latest member
cve60069

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top