V
vcff
Hi all
Is there a way to modify the follow code so that the title in the TOC
worksheet link to a fixed cell (eg A1) rather than the tab "name" in which
the length of wording was limited.
(the following code was from JW who had helped me in my previous ?) tnks
Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw = ActiveWorkbook.Worksheets _
.Add(Before:=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[A1] = "Table Of Contents"
.[A2] = ActiveWorkbook.Name & " Worksheets"
.[A1].Font.Size = 14
.[A2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
End Sub
Is there a way to modify the follow code so that the title in the TOC
worksheet link to a fixed cell (eg A1) rather than the tab "name" in which
the length of wording was limited.
(the following code was from JW who had helped me in my previous ?) tnks
Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw = ActiveWorkbook.Worksheets _
.Add(Before:=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[A1] = "Table Of Contents"
.[A2] = ActiveWorkbook.Name & " Worksheets"
.[A1].Font.Size = 14
.[A2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
End Sub