HYPERLINK() function not working with VBA .Follow

[Babolat25]

Hello,
I'm not a power user but I tried to google my problem for hours and couldn't find an answer so I landed here seeking for help.

The situation
I have made a URL with CONCAT function that looks something like https://blablabla.com/date/something/variables. In another cell I used HYPERLINK() to get a link out of that. With hundreds of these entries my goal is to run a VBA macro that opens in new tabs the ones in selected cells:

Sub OpenLinks()
    Dim a As Hyperlink
    For Each a In Selection.Hyperlinks
        a.Follow
    Next a
End Sub

The problems
1) HYPERLINK()
What I get is a text formatted as an hyperlink that works if I click on it but is not recognized as hyperlink by the macro. The only links opened by the macro are the ones I copy from the browser. Is it normal? What can I do?

2) Copy-Paste a list of links
A secondary problem I encountered while testing a fix: if I copy a single link into excel it's correctly seen as a link but if I copy a list of links they're pasted as text. I couldn't find a way to paste them as link. Can you help me with that too?

Thanks in advance,
Andrea

 

[lrobbo314]

The 'Hyperlink' function isn't the same as actually adding a hyperlink that can be referenced in the way you are doing above.

Something like this seems to work. This loops through each cell in the selection. Adjust as needed.

Sub OpenLinks()
Dim cel As Range
For Each cel In Selection
    ActiveWorkbook.FollowHyperlink cel.Value
Next cel
End Sub

 

[Babolat25]

Thank you very much.
When I run it the first link is opened correctly and then it pops-up "runtime error 5: invalid procedure call or argument" on this line "ActiveWorkbook.FollowHyperlink cel.Value". What should I change?

I'm sorry if it's an obvious adjustment but I'm new to vba coding.