Insert Tab name into worksheet?

[scewing]

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!

 

[kskinne]

[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]

=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]

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]

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]

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]

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,

 

[jyokom]

Works like a charm!

 

[Dexter77]

Wawo, Fabulous , What the hell is this.?!!! Thaaaaaaaaaaaaaankss

 

[Smitty]

It's VBA code. Do you need to know how to use it?