[Tom Ogilvy]

Change
SubAddress:=ws.Name & "!A1

to

SubAddress:="'" & wsName & "'!A1"

Note that I have put -- doublequote singlequote doublequote & <-- before
swName

and in "!A1" I have added a singlequote after the first doublequote.

Regards,
Tom Ogilvy


onliner wrote in message
news:hMfQa.925$Ze.806@fed1read03...
The following code creates a Table of Contents complete with hyperlinks to
each of the worksheets within the workbook.
The problem is that the hyperlinks only work for sheets with one name, for
example, "sheet1". For sheets with multi-word names like "sheet one", the
hyperlinks don't work.
Anyone know how to fix this?

Sub TableOfContents2()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub

[onliner]

Thanks, Tom.
Did you get a chance to test this?
I can't get it to work.
Appreciate your feedback.


"Tom Ogilvy" wrote in message
...
Change
SubAddress:=ws.Name & "!A1

to

SubAddress:="'" & wsName & "'!A1"

Note that I have put -- doublequote singlequote doublequote & <--
before
swName

and in "!A1" I have added a singlequote after the first doublequote.

Regards,
Tom Ogilvy


onliner wrote in message
news:hMfQa.925$Ze.806@fed1read03...
The following code creates a Table of Contents complete with hyperlinks
to
each of the worksheets within the workbook.
The problem is that the hyperlinks only work for sheets with one name,
for
example, "sheet1". For sheets with multi-word names like "sheet one",
the
hyperlinks don't work.
Anyone know how to fix this?

Sub TableOfContents2()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub

[Tom Ogilvy]

Table of Contents

Sheet5
Master
Sheet 3
Sheet one
Master (2)
Master (3)


is an example and all the hyperlinks worked.

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Yes, before posted, I did and it worked fine.

Perhaps you are not making the change correctly

Here is the whole routine - copy and paste it into a new module zand test
it

Sub TableOfContents2_AAA()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add _
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:= _
wsTOC.Cells(r, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name
'wsTOC.Cells(r, 1).Value = ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub

I tested it in Excel 97 which did not have the TextToDisplay argument, so
I
used the value of the cell, but I have commented out that line and
restored
the TextToDisplay argument.


--
Regards,
Tom Ogilvy


onliner wrote in message
news:53iQa.942$Ze.503@fed1read03...
Thanks, Tom.
Did you get a chance to test this?
I can't get it to work.
Appreciate your feedback.


"Tom Ogilvy" wrote in message
...
Change
SubAddress:=ws.Name & "!A1

to

SubAddress:="'" & wsName & "'!A1"

Note that I have put -- doublequote singlequote doublequote & <--
before
swName

and in "!A1" I have added a singlequote after the first doublequote.

Regards,
Tom Ogilvy


onliner wrote in message
news:hMfQa.925$Ze.806@fed1read03...
The following code creates a Table of Contents complete with
hyperlinks
to
each of the worksheets within the workbook.
The problem is that the hyperlinks only work for sheets with one
name,
for
example, "sheet1". For sheets with multi-word names like "sheet
one",
the
hyperlinks don't work.
Anyone know how to fix this?

Sub TableOfContents2()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub

[onliner]

Thanks, Tom.
Worked perfectly.


"Tom Ogilvy" wrote in message
...
Table of Contents

Sheet5
Master
Sheet 3
Sheet one
Master (2)
Master (3)


is an example and all the hyperlinks worked.

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Yes, before posted, I did and it worked fine.

Perhaps you are not making the change correctly

Here is the whole routine - copy and paste it into a new module zand
test
it

Sub TableOfContents2_AAA()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add _
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:= _
wsTOC.Cells(r, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name
'wsTOC.Cells(r, 1).Value = ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub

I tested it in Excel 97 which did not have the TextToDisplay argument,
so
I
used the value of the cell, but I have commented out that line and
restored
the TextToDisplay argument.


--
Regards,
Tom Ogilvy


onliner wrote in message
news:53iQa.942$Ze.503@fed1read03...
Thanks, Tom.
Did you get a chance to test this?
I can't get it to work.
Appreciate your feedback.


"Tom Ogilvy" wrote in message
...
Change
SubAddress:=ws.Name & "!A1

to

SubAddress:="'" & wsName & "'!A1"

Note that I have put -- doublequote singlequote doublequote &
<--
before
swName

and in "!A1" I have added a singlequote after the first
doublequote.

Regards,
Tom Ogilvy


onliner wrote in message
news:hMfQa.925$Ze.806@fed1read03...
The following code creates a Table of Contents complete with
hyperlinks
to
each of the worksheets within the workbook.
The problem is that the hyperlinks only work for sheets with one
name,
for
example, "sheet1". For sheets with multi-word names like "sheet
one",
the
hyperlinks don't work.
Anyone know how to fix this?

Sub TableOfContents2()

Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set wsTOC = ActiveWorkbook.Worksheets.Add
(befo=ActiveWorkbook.Sheets(1))
wsTOC.Name = "Table of Contents"
wsTOC.Range("A1") = "Table of Contents"
wsTOC.Range("A1").Font.Size = 18
r = 3
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < wsTOC.Name Then
wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="",
_
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
r = r + 1
End If
Next
Application.ScreenUpdating = True
End Sub