recreate hyperlink, or create net hyperlink VBA excel

Question

I have 2 sheets. In one of the sheet (sheet1) I have 1 hyperlink with file address \sii\picture\as.jpg hyperlink is named "LINK". I need to re create this hyperlink without "formats" (background color and any of them). I need to do this using VBA.

when I try this I get an error

Localization = Sheets("Sheet1").Range("A1").Hyperlinks(1).Address

Sheets("Sheet2").Hyperlinks.Add Anchor:=Sheets("sheet2").Cells(2, 2), Address:=Localization, TextToDisplay:="LINK"

if I make something like

sheets("Sheet1").select

range (Cells(1,1)).select

SELECTION.Copy

sheets("Sheet2").select

range (Cells(1,1)).select

Sheets("Sheet2").Paste

I get good hiperlink but this hiperlink have formated background and any things from first sheet, and my screen is blinking because excel is jumping between sheet1 and sheet2

To recover link correct the first line: Localization = Sheets("Sheet1").Hyperlinks(1).Address after that, work fine — user3514930
If you leave ". Range("A1")" you need to know the number of hyperlink. after all i make it in one line and it works. Sheets("Sheet2").Hyperlinks.Add Anchor:=Sheets("Sheet2").Cells(2, 1), Address:=(Sheets("Sheet1").Range("A1").Hyperlinks(1).Address), TextToDisplay:="LINK" — byrqowy

Adach1979 Adach1979 · Accepted Answer · 2014-05-06T16:41:40

This should work for you. You may have to amend it to fit withing your existing code

Sub Main()

Dim rngDest As Range
Dim rngSource As Range
Dim wkshtSource As Worksheet
Dim wkshtDest As Worksheet
Dim urlSource As String

    ' Set Worksheet and Range variables for easy reference
    Set wkshtSource = ActiveWorkbook.Sheets("Sheet1")
    Set wkshtDest = ActiveWorkbook.Sheets("Sheet2")
    Set rngSource = wkshtSource.Cells(1, 1)
    Set rngDest = wkshtDest.Cells(2, 2)
    'Gets the address of the hyperlink to copy
    urlSource = rngSource.Hyperlinks(1).Address

    'Creates Hyperlink on second page
    rngDest.Hyperlinks.Add Anchor:=rngDest, Address:=urlSource, TextToDisplay:="Link"

End

recreate hyperlink, or create net hyperlink VBA excel

1 Answers