VBA Syntax help, adding a variable value to a hyperlink macro

[imol73]

Hi All, I would really appreciate some help with a macro to hyperlink a cell on Sheet 1 to a cell on Sheet 2. Column A on Sheet 1 contains the cell reference to Sheet 2 and Column B, Sheet 1 is where I want the the hypelink to appear. I've had some minor success with the following macro...

Sub Hlink()

Dim strMyValue As String
strMyValue = ActiveCell.Offset(0, -1).Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Sheet 2' strMyValue"

End Sub

...but the resulting hyperlink returns 'Refernce is not valid' when clicked. It appears that the ActiveSheet.Hyperlinks.Add command is not correctly formated to use the value of strMyValue.

I'm a real newbie to this stuff so please let me know if you need more info.

Thanks in advance

 

[Jubjab]

The subaddress is with wrong syntax, it reads is literally now. Change it to

SubAddress:= "'Sheet 2'!" & strMyValue

 

[imol73]

Thank you so much JubJab. That worked perfectly and saved me a lot of time. To save even more, how could I apply this to a range of cells so that each is cell (Sheet 1, B10:B862) is hyperlinked to Sheet 2 using the values in Sheet 1, A10:A862?

Thanks again!

 

[imol73]

UPDATE:
I borowed from a few other Macros I found on the web and came up with...

Sub HlinkOnRange()
'
'
Dim cell As Range
Dim myRange As Range
Dim strMyValue As String
Set myRange = Sheets("Sheet 1").Range("B2:B834") '<<adjust range to suit

For Each cell In myRange.Cells

strMyValue = cell.Offset(0, -1).Value
ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:= _
"'Sheet 3'!" & strMyValue
Next cell

End Sub

...which worked a treat so that's two problems solved! The next problem is giving me headaches though. I have a list of site names in Column B of Sheet 3 and a heap of individual worksheets named from this list (using another Macro). I now need to hypelink the names in Column B to cell A2 of each individual site sheet so i came up with this...

Sub HlinkToSiteSheet()

Dim cell As Range
Dim myRange As Range
Dim strMyValue As String
Set myRange = Sheets("Sheet 3").Range("B2:B134") '<<adjust range to suit

For Each cell In myRange.Cells

strMyValue = cell.Value
ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:= _
strMyValue & "!A2"
Next cell

End Sub

...this almost worked perfectly but about 30% of the hypelinks returned 'Refernce is not valid'. Further investigation showed that some sheet names were in apostrophes and those were the ones that didn't work. i.e when I right click a cell, Edit Hypelink, the list of possible pages looks something like...

Cell Refernce
Sheet 1
Sheet 2
Sheet 3
Site 1
'Site 2'
'Site 3'
Site 4

The macro works fine on the Site 1 and Site 4 cells but not on Site 2 and Site 3. Any tips on how to get around this?