Forum Discussion

tylerx's avatar
tylerx
Copper Contributor
Nov 13, 2023

Pasting a hyperlink without the formulas

Good morning,  I have created a database that contains over 7,000 devices, each of these devices have several links that are attached. While building this database I put several links in the ...
Formulas and Functions
tylerx's avatar
tylerx
Copper Contributor
Nov 13, 2023
That is what I thought would work but all it does is paste the text with no link or formula. I have been at this for 3 days and tried paste options, formating cells, and extracting url's. Havent found an easy way yet
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 13, 2023

    tylerx 

    I've found the below code in the www and it changes e.g.

    =HYPERLINK("C:\Users\olive\OneDrive\Bilder\if.png")

    to

    C:\Users\olive\OneDrive\Bilder\if.png

    and keeps the hyperlink active.

    Sub Test()
    Dim Cell As Range
    For Each Cell In Range("D1:D10")
        With Cell
            .Value = .Value
            .Hyperlinks.Add .Cells(1, 1), .Value
        End With
    Next Cell
End Sub

    The Range("D1:D10") can be changed according to the actual size of the data.

     

    Active hyperlink without formula after running the macro.

     

    Active hyperlink with HYPERLINK formula before running the macro.

     

     

Resources