Dynamic Hyperlink Address

[excelschlub]

Hello,

I need to find a way to make the subaddress of my hyperlinks change as rows in my sheet are deleted. Essentially, the code I have below creates a hyperlink on the same line that input from a userform is transferred to. When the user clicks the newly created hyperlink, they move the corresponding line of data from the current sheet to a 'completed items' sheet. My problem is that as lines are moved from one sheet to another, the hyperlinks are continuing to reference their original placement address rather than their current cell address which means that when these hyperlinks are clicked, they are moving the wrong line of data.

I have done reading that suggested referring to a named range will solve my problem, but I'm not sure how to dynamically name each range within the hyperlink creation code. As info, all the hyperlinks have the same name, but each has a unique address. Again, the code that creates the hyperlink once the userform is shown below.

ActiveSheet.Hyperlinks.Add anchor:=Cells(NextRow, 11), _
Address:="", _
SubAddress:="'" & Sheet16.Name & "'!" & Cells(NextRow, 11).Address, _
TextToDisplay:="Mark as Complete"

As usual, I'm up for any ideas you may have. Many thanks!

-Dave

 

[excelschlub]

Ok, I've tried to figure this out my myself, but I am having trouble referencing the name of my range in the subaddress. I was hoping someone might be able to help? Code below:

Sub test123()


r = ActiveCell.Row
c = ActiveCell.Column


Dim Rng1 As Range
     
'Define named range
Set Rng1 = Sheets("Sheet1").Cells(r, 1)
ActiveWorkbook.Names.Add Name:=Cells(r, 2), RefersTo:=Rng1


'Insert hyperlink
ActiveSheet.Hyperlinks.Add anchor:=Cells(r, 1), _
Address:="", _
SubAddress:=Range(Rng1.Address).Name, _
TextToDisplay:="Complete " & Cells(r, c)


End Sub

So the 'SubAddress' is where I am having trouble. This code will work if I put a cell in, but I cant get it to reference the range I created as Rng1. Any suggestions?

Thanks,
Dave

 

[excelschlub]

Ok - I fixed it. I used the following:

SubAddress:=Rng1.Name.Name, _

Hope this may help someone.