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!

 

[Jonesyboy]

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

Sub CreateTOC()
****'** Code by Zack Baresse
****If ActiveWorkbook Is Nothing Then
********MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
********Exit Sub
****End If
****
****With Application
********.ScreenUpdating = False
********.DisplayAlerts = False
****
********Dim ws As Worksheet, _
************ct As Chart, _
************shtName As String, _
************nrow As Long, _
************tmpCount As Long, _
************i As Long, _
************numCharts As Long
********
********nrow = 3
********i = 1
********numCharts = ActiveWorkbook.Charts.Count
********
********On Error GoTo hasSheet
********Sheets("Table of Contents").Activate
********If MsgBox("You already have a Table of Contents page.**Would you like to overwrite it?", _
********vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
********Exit Sub

hasSheet:
****Sheets.Add Before:=Sheets(1)
****GoTo hasNew

createNew:
****Sheets("Table of Contents").Delete
****GoTo hasSheet

hasNew:
****tmpCount = ActiveWorkbook.Charts.Count
****If tmpCount > 0 Then tmpCount = 1
********ActiveSheet.Name = "Table of Contents"
********
********With Sheets("Table of Contents")
************'.Cells.Interior.ColorIndex = 4
****************With .Range("B2")
********************.Value = "Table of Contents"
********************.Font.Bold = True
********************.Font.Name = "Calibri"
********************.Font.Size = "24"
****************End With
********End With
********
********For Each ws In ActiveWorkbook.Worksheets
************nrow = nrow + 1
************With ws
****************shtName = ws.Name
****************With Sheets("Table of Contents")
********************.Range("B" & nrow).Value = nrow - 3
********************.Range("C" & nrow).Hyperlinks.Add _
************************Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _
************************shtName & "'!A1", TextToDisplay:=shtName
********************.Range("C" & nrow).HorizontalAlignment = xlLeft
****************End With
************End With
********Next ws
********
********If numCharts <> 0 Then
************For Each ct In ActiveWorkbook.Charts
****************nrow = nrow + 1
****************shtName = ct.Name
****************With Sheets("Table of Contents")
********************.Range("B" & nrow).Value = nrow - 3
********************.Range("C" & nrow).Value = shtName
********************.Range("C" & nrow).HorizontalAlignment = xlLeft
****************End With
************Next ct
********End If
********
********With Sheets("Table of Contents")
************With .Range("B2:G2")
****************.MergeCells = True
****************.HorizontalAlignment = xlLeft
************End With
********
************With .Range("C:C")
****************.EntireColumn.AutoFit
****************.Activate
************End With
************.Range("B4").Select
********End With
****
********.DisplayAlerts = True
********.ScreenUpdating = True
****End With
****
****MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _
********"Charts are listed after regular " & vbCrLf & _
********"worksheets and will not have hyperlinks.", vbInformation, "Complete!"

End Sub

HTH,

Hi Guys,

Apologies for gatecrashing a super thread, can i ask an additional question to the above code please.

I have a workbook where i have around 25 No worksheets and when i use the above code it neatly gives a table of contents as described.

My problem is if i rename any of the worksheet name tabs the hyperlink (in the Table of Contents) to that sheet breaks down.......a pop up window says (Reference is not valid).

Is this an easy solution?

I am new to excel and have no VBA skills whatsoever, can somebody help.

Thanks in advance.

Paul J

 

[Smitty]

Welcome to the Board!

If you rename a sheet you simply rerun the code.

HTH,

 

[Jonesyboy]

Welcome to the Board!

If you rename a sheet you simply rerun the code.

HTH,

Hi Smitty

Thanks for the welcome and quick response!

I did what you suggested and it worked, however it deleted stuff I'd copied across from my index page to the new table of contents page.

I already have an index page with a table of contents but my original problem (my index page) is if i change the worksheet tab name it breaks the hyperlink hence why I'd copied your code thinking that would solve my problem.

Again i apologize for me being illiterate with excel but I've only been using it more frequently for a little over 4 months, any suggestion(s) would be much appreciated.

Again thanks in advance.

Paul J.

 

[Smitty]

If you already have an existing Index sheet, then you'll need to manually adjust the hyperlinks.

As why anything that you may have copied to the newly created Table of Contents gets deleted, it's because the TOC sheet is first deleted if it already exists, then recreated from scratch.

 

[Jonesyboy]

If you already have an existing Index sheet, then you'll need to manually adjust the hyperlinks.

As why anything that you may have copied to the newly created Table of Contents gets deleted, it's because the TOC sheet is first deleted if it already exists, then recreated from scratch.

That's what I'm doing already.

Never mind, thanks for looking and your time, Cheers.

 

[Ccc Rrr]

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

HTH
Chris

Just an note that the sheet must be SAVED first -- won't work with a new sheet if you try that! (so I found...) Thus, the "address" part!