VBA: Copy Hyperlink without the text from another worksheet

[Kristina96]

Hello,

I am trying to copy a hyperlink from cell A2 from Worksheet 2 to cell B3 of worksheet 1. The text in both cells should remain unchanged.
Any help would be very much appreciated.

Thank you in advance
Kristina

 

[Crystalyzer]

There may be more elegant solutions to this and this is VERY SPECIFIC to your request above (i.e. cells and worksheets) but here goes

Sub CopyURL()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim urlStr As String, tmpVal As String
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    urlStr = ws1.Range("A2").Hyperlinks(1).Address
    
    With ws2
        .Activate
        .Range("B3").Select
        tmpVal = .Range("B3").Value
        Selection.Hyperlinks.Delete
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=urlStr, TextToDisplay:=tmpVal
    End With
    
End Sub