Pasting a hyperlink without the formulas

Question

Good morning,&nbsp;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 wrong spot. I caught the mistakes, cleared the contents and pasted in the correct links.&nbsp; Not sure how it happened but when i cleared the contents, the text cleared the cell but the link remained.&nbsp; So when I pasted the correct link, it looked correct but it takes you to the wrong link.&nbsp; I have created a formula that worked to replace all the links with the correct link.&nbsp;The problem is that now my database is extreamly slow.&nbsp; Is there any way to copy and paste the hyperlink without the formula?????

tylerx · Answer

Sorry, this is the formula i used:=IFERROR(HYPERLINK(url(INDEX(Sheet2!$J$2:$J$934,XMATCH('Eagle LSS Database'!K24,Sheet2!$J$2:$J$934))),'Eagle LSS Database'!K24),"")Since there were blank cells that still had links, i had to use the formula for the cells with the links and the blank cells. It worked but now i have over 93,000 cells with this formula and i believe that is why it is slow.

oliverscheurich · Answer

tylerx&nbsp;If i correctly understand you can use copy and paste only values (formula results, without formatting or comments).Paste options - Microsoft Support

tylerx · Answer

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 · Answer

tylerx&nbsp;I've found the below code in the www and it changes e.g.=HYPERLINK("C:\Users\olive\OneDrive\Bilder\if.png")toC:\Users\olive\OneDrive\Bilder\if.pngand 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 SubThe Range("D1:D10") can be changed according to the actual size of the data.&nbsp;Active hyperlink without formula after running the macro.&nbsp;Active hyperlink with HYPERLINK formula before running the macro.&nbsp;&nbsp;

Forum Discussion

Pasting a hyperlink without the formulas

Share

Resources