Hyperlinks in Macro

[mwc360]

Hi,

Thank you all for your help in advance. I need help writing the code to create a hyperlink that links to a sheet titled the same as the the cell that is currently being selected. I also want the hyperlink to be titled the same as the value already in the current cell.

I want the subaddress to be the sheet that was just created (Sheets(Sheets.Count).Name)and the TextToDisplay to be name of the sheet it is linking to or the value of the current cell that contains the hyperlink (Active.Selection.Value).

I get an error when I write the code this way. Can anyone please help me correct write the code for the hyperlink.

I am using Excel 2010

Thanks!

Sub Macro10()
'
' Macro10 Macro
'

'
    ActiveWorkbook.Save
    Sheets("Notes Template").Select
    Sheets("Notes Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Project Schedule").Select
    ActiveCell.Select
    Selection.Copy
    Sheets(Sheets.Count).Select
    Range("$B$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets(Sheets.Count).Name = ActiveSheet.Range("$B$2")
    Sheets("Project Schedule").Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    Selection.Copy
    Sheets(Sheets.Count).Select
    ActiveCell.Offset(2, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   [COLOR=#008000] 'HYPERLINK => THIS IS WHAT I NEED HELP WITH[/COLOR]
    Sheets("Project Schedule").Select
    ActiveCell.Offset(0, -4).Range("A1").Select
    [COLOR=#ff0000]ActiveSelection.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(Sheets.Count).Name & "!A1", TextToDisplay:=ActiveSelection.Value[/COLOR]
End Sub

 

[Andrew Poulsom]

Maybe try (untested):

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(Sheets.Count).Name & "!A1", TextToDisplay:=Selection.Value

 

[mwc360]

That worked!! Thanks so much!