Solved: VBA Hyperlink Problem

[maxhayden]

Hello there.

I have a peice of code that creates a new worksheet and names in Link1, 2 , 3... etc depending on the sheet order:

[vba] .Copy after:=Sheets(Sheets.Count)

ActiveSheet.Name = "Link " & Worksheets.Count - 2 [/vba]

I want to create a hyperlink to each new worksheet. I did this using the following code but the link doesn't work:

[vba]

ActiveSheet.Cells(h, 5).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Link" & Worksheets.Count - 2, TextToDisplay:="Click Here"[/vba]

Can someone tell me what I'm doing wrong??

Ps. When I move my mouse over the "Click Here" link, ti comes up saying Link1 (so the naming must work). I think it is missing the !A1 cell reference at the end, but I'm not sure how to fit that in.

Thanks in advance for your help.

PS. And extra thought... It says Link1 on the hyperlink... when it should say Link 1... (missing the space!!) Hmmm...

[maxhayden]

ITS OK!!! I SOLVED IT!!! HAHAHA!. It was the stupid space!! "Link " Rather than "Link"

!!!!

[maxhayden]

Sorry! I thought I figured it out but was wrong... the space didn't work!!! Even though when I scroll the mouse over the link it comes up exactly the same as one that works!!! ?????

Hello there.

I have a peice of code that creates a new worksheet and names in Link1, 2 , 3... etc depending on the sheet order:

[VBA].Copy after:=Sheets(Sheets.Count) ActiveSheet.Name = "Link " & Worksheets.Count - 2 [/VBA]

I want to create a hyperlink to each new worksheet. I did this using the following code but the link doesn't work:

[VBA]ActiveSheet.Cells(h, 5).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Link " & Worksheets.Count - 2, TextToDisplay:="Click Here" [/VBA]

Can someone tell me what I'm doing wrong??

Ps. When I move my mouse over the "Click Here" link, ti comes up saying Link1 (so the naming must work). I think it is missing the !A1 cell reference at the end, but I'm not sure how to fit that in.

Thanks in advance for your help.

PS. And extra thought... It says Link1 on the hyperlink... when it should say Link 1... (missing the space!!) Hmmm...

[maxhayden]

Woops!!! That didn't work!! I got ahead of myself there! How bizzare! The link it creates is EXACTLY the same as one that I create manually - but the macro-made one doesn't work!

??? Hmmm

[Bob Phillips]

[vba]

Dim sh As Worksheet
Dim h As Long
h = 8
Set sh = ActiveSheet
With sh

.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Link " & Worksheets.Count - 2
.Cells(h, 5).Hyperlinks.Add Anchor:=.Cells(h, 5), Address:="", SubAddress:= _
"'Link " & Worksheets.Count - 2 & "'!A1", TextToDisplay:="Click Here"
End With
[/vba]

I have merged these threads.

[maxhayden]

Thanks!! It was the sneaky ' before the link and ' before the !A1 that i missed out!

Thanks again for your help.

[Bob Phillips]

Yes, you always need that with embedded spaces in a worksheet name.