Create Hyperlinks based on selection and cell contents

[keating101]

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


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

[June7]

Try:

newCell.Text

or

newCell.Value

instead of ActiveCell.Text

[keating101]

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

[Paul_Hossler]

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



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




30650

[Aussiebear]

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


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.