VBA insert dynamic hyperlink using cell value

[nryan]

Hello. I'm trying to insert hyperlinks into a table using VBA.
I have values in column A (starting at "A5" because of headers). Each of those values are a suffix of a web address.
I want to insert a hyperlink in each cell using a web address ("www. website .com/") and the cell value so the link is www. website .com/"cell A value".

I looked at a few other posts to help me get started:
1. Use VBA to hyperlink based on cell text (Mr. Excel)
2. Adding Hyperlinks based on cell contents (Mr. Excel)
3. VBA to create Hyperlink including cell value (Mr. Excel)

I tried dropping the code from these posts in my workbook but they didn't work for me. The first link almost worked but stopped after the first row and I couldn't figure out how to get it to work for all the other rows.
Here's what my table looks like (couldn't figure out how to shrink column width):

row	A
1	Table info stuff
2	More table info
3
4	Header
5	100445.1
6	100552.1
7	100556.3
8	100652.1
...

My code:

Sub createLink()

    Dim x As Integer
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    With ThisWorkbook.Worksheets("Sheet2").Activate
        For x = 5 To LastRow
            Cells(x, "A").Activate
            .Hyperlinks.Add Anchor:=ActiveCell, Address:="www.website.com/" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text
        Next x
    End With

End Sub

I'm getting a run-time error (424, object required) at the .Hyperlinks.Add line that I cannot figure out how to fix.
I checked my variables and syntax and they look okay to me. In the debugger I see that my variables are showing up (see image below).

I've been troubleshooting for a few hours with no luck.

Important note: My table is a database table I created using power query. I suspected this was maybe causing problems so I created a mock table on another sheet that is not a power query table, just the column "A" data I copied over. My code has the same error on both tables, power query and non-power query. So maybe it's trivial.

Thank you for reading.
-Nick

 

[LazyBug]

Hello!
Try to change the line

With ThisWorkbook.Worksheets("Sheet2").Activate

with

With ActiveWorkbook.Worksheets("Sheet2")

 

[nryan]

Hi Lazybug. That change did the trick!!
Thank you.
Seems so basic of a thing to get wrong on my end. I'm glad you could help me.

 

[nryan]

Update.
In the Power Query table this code works! But after clicking one of the hyperlinks all hyperlinks are "deactivated" meaning they don't work anymore. Maybe the power query table updates and erases the hyperlinks. I'm not sure. I found a workaround by placing the code in the worksheet instead of in a module. Code in the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim x As Integer
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    With ActiveWorkbook.Worksheets("Sheet1")
        For x = 5 To LastRow
            Cells(x, "A").Activate
            .Hyperlinks.Add Anchor:=ActiveCell, Address:="www.website.com/" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text
        Next x
    End With

Range("A3").Select  'Useful for long tables. Takes you back to the top of the table.

End Sub

Thanks again LazyBug.

 

[LazyBug]

Unfortunately PQ isn't my strong point, so can't help with this question. I don't understand your goal by the hyperlinks adding with every Worksheet_Change event, but i'm glad if it works for you. Thanks for the feedback,