VBA macro to extract Hyperlink from one cell to another

Question

I have the following code that extracts a hyperlink from a cell and then moves it to an adjacent cell. My goal is to be able to specify multiple columns and the exact column the cells should move to.

As you can see, the code is taking the hyperlink value from columns B1 to B2000 and copying the hyperlink value to column C (cll.Column + 1), but I would like to specify the exact column I want to copy the hyperlink to.

For example: I want all hyperlinks to copy from B to G; D to F; and E to H.

Sub hyper()
 Dim sht As Worksheet: Set sht = Worksheets("Sheet1")
 Dim cll As Range
 For Each cll In sht.Range("B2:B2000")
 If cll.Hyperlinks.Count > 0 Then
     sht.Cells(cll.Row, cll.Column + 1).Value = cll.Hyperlinks(1).Address
 End If
   Next cll
 msgbox "The macro has completed running"
 End Sub

I don't much about VBA macros so any help is appreciated. Thank you very much! ~Adam

Vityata · Accepted Answer · 2018-05-30 15:54:09Z

2

Select Case can improve your code. Just make sure that you fix the Magic Numbers within as you wish:

Sub Hyper()
    Dim sht As Worksheet: Set sht = Worksheets("Sheet1")
    Dim cll As Range
    For Each cll In sht.Range("B2:H2000")
        If cll.Hyperlinks.Count > 0 Then
            Select Case cll.Column

            Case Range("A1").Column:
                sht.Cells(cll.Row, 15).Value = cll.Hyperlinks(1).Address
            Case Range("E1").Column
                sht.Cells(cll.Row, 20).Value = cll.Hyperlinks(1).Address
            Case Range("G1").Column
                sht.Cells(cll.Row, 25).Value = cll.Hyperlinks(1).Address

            End Select
        End If
    Next cll
    MsgBox "The macro has completed running"
End Sub

answered May 30, 2018 at 15:54

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adam Over a year ago

Wow, this Is really great. It works perfect. Thank you so much!

Adam Over a year ago

quick question, is there a way to specify column AA instead of (cll.row, 27) for example? Just curious. thank you!

BigBen Over a year ago

@Vityata - maybe I'm misunderstanding, but couldn't OP just use Cells(cll.Row, "AA") - i.e. the column index can be a number or string?

Vityata Over a year ago

@Adam - take a look at the comment above.

Adam Over a year ago

"AA" worked. Thank you both for being rockstars, very very much appreciated!

|

Collectives™ on Stack Overflow

VBA macro to extract Hyperlink from one cell to another

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related