Create Hyperlinks based on selection and cell contents

hello all and thanks in advance

im trying to create a macro whereby a user can select a range of cells, runs a macro and a hyperlink is automatically to each cell. The hyperlink will be a combination of static text and whatever is in each cell.

below is what i have started - it is adding a hyperlink to all the cells, but the problem is the actual hyperlink URL is not updating for each subsequent cell and its contents - it is showing the same hyperlink for all cells based on the first active cell

thanks
Shaun

Code:

---

Sub Hyperlink()
Dim newRange As Range
Dim newCell As Range
Set newRange = Selection
For Each newCell In newRange
  With ActiveSheet
      .Hyperlinks.Add Anchor:=newRange, _
      Address:="pronto:%20/u/pronto/data/L25%20drilldown%20job-code%20" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text
  End With
Next newCell
End Sub

---

Try:

newCell.Text

or

newCell.Value

instead of ActiveCell.Text

Sorry Jun7 - no good (same result) ...

I think this is what you were asking

I made a few cleanups. I don't like to use Activesheet, eiither a named one or a range's parent, since you can never count on the sheet that you "think'" is active actually being the ActiveSheet. Personal opinion -- your choice

Code:

---

Option Explicit

Sub AddHyperlinksToSelection()
    Dim newCell As Range

    For Each newCell In Selection
        Selection.Parent.Hyperlinks.Add _
            Anchor:=newCell, Address:="pronto:%20/u/pronto/data/L25%20drilldown%20job-code%20" & newCell.Text, TextToDisplay:=newCell.Text
    Next newCell
End Sub

---

Attachment 30650

Very similar to Paul's, but in this case naming the sheet.

Code:

---

Option Explicit
 
Sub AddHyperlinksToSelection()
Dim newCell As Range
With Sheets(“Sheet1”) ---< Change this to suit actual sheet name
    For Each newCell In Selection
        Selection.Parent.Hyperlinks.Add _
        Anchor:=newCell, Address:="pronto:%20/u/pronto/data/L25%20drilldown%20job-code%20" & newCell.Text, TextToDisplay:=newCell.Text
    Next newCell
End With
End Sub

---

My first impression with the original code was that the loop was started and then you went to the With, End With whereas both Paul and myself have the loop performing inside of the With, End With.