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