Excel VBA copy hyperlink from a cell to a column in a table

Question

I would like to copy rows from a source sheet to a target table. Coping the values is no problem.

The column A in the source sheet contains a hyperlink to a website.
I would like to copy the hyperlink too.

However this code gives an error (type mismatch).

Sub Copy_Hyperlink()
    Dim ActiveSheet As Worksheet
    Dim ActiveTable As ListObject
    Dim Row_Number As Integer
    Dim Last_Row As Integer
    Dim ActiveRow As ListRow
    Dim Hyperlink As Hyperlink
    Set ActiveSheet = Sheets("Sheet2")
    Set ActiveTable = ActiveSheet.ListObjects("Table1")
    Set ActiveSheet = Sheets("Sheet1")    

    ActiveSheet.Activate
    Last_Row = ActiveSheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row

    For Row_Number = 2 To Last_Row
        Set ActiveRow = ActiveTable.ListRows.Add
        ActiveRow.Range(1, 1).Value = Range("A" & Row_Number).Value
        ActiveRow.Range(1, 2).Value = Range("B" & Row_Number).Value
        ActiveRow.Range(1, 2).Hyperlinks.Add Range _
    ("A" &   Row_Number).Hyperlinks.Item(1).Address
    Next Row_Number
End Sub

Answer 1

Your code has a few issues but without explanation or responses we won't be able to give you a definitive answer.

I am not convinced that everything is working fine except for adding the hyperlink, but you can confirm that by commenting out the line that adds the hyperlink and running your code again.

' ActiveRow.Range(1, 2).Hyperlinks.Add Range("A" & Row_Number ).Hyperlinks.Item(1).Address

If the code works fine now, then there could still be a number of issues.

I assume you are probably trying to get a hyperlink from a cell that doesn't have a hyperlink (but I don't know since you didn't share your data.) If so you can use Hyperlinks.Count to check if there is a usable link.

You are definitely using Hyperlink.Add incorrectly.

Read more here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/hyperlinks-add-method-excel?f=255&MSPPError=-2147217396 and then edit your question to add more information.