Dynamic Hyperlinks

**acmichae** · 03-23-2021, 10:10 PM

Hello,

I have a spreadsheet of data where each cell of data needs to be hyperlinked to a corresponding PDF file. Say one cell in this column of 100 rows is "M0004125". This needs to be hyperlinked so that when clicked, it opens up the PDF file named "M0004125" in the folder 'C:\Users\Myself\Documents\Data Files"'. While I am able to hyperlink each cell individually through "right-click and hyperlink", this is very time consuming since I have so many data points to hyperlink. Is there a function out there that allows me to type out once, and then drag down through all cells in that column?

Thank you!

**arunsjain** · 03-26-2021, 12:01 AM

Hi

Try following code. I assumed list of files in Sheet1, Column A and starts from Cell "A2". If not, you can update it accordingly. I hope it will help. Cheers!!!

Sub Add_Hyperlink()
    
Dim Lrow As Integer
Dim iR As Integer

Lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
    For iR = 2 To Lrow
    
        If Cells(iR, 1) <> "" Then
    
            Sheet1.Hyperlinks.Add Anchor:=Cells(iR, 1), _
            Address:="C:\Users\Myself\Documents\Data Files\" & Cells(iR, 1).Value & ".pdf", _
            TextToDisplay:=Cells(iR, 1).Value

        End If

    Next iR

End Sub

**acmichae** · 03-26-2021, 02:53 AM

Thank you Arunsjain, that worked!

Dynamic Hyperlinks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View