VBA to open the url in column CN

[Knockoutpie]

Hi all, I have the following code to loop through and open the URL in column CN, but i'm getting some issues.

The URL is the result of an IF statement, and can be one of 5 websites.
Physical clicks on the cell to open work fine, but i cannot figure out to how get VBA to open.

Links will start in cell CN2.

    Sub OpenURLs()

Dim i As Integer

For i = 1 To ActiveSheet.Range("CN" & Rows.Count).End(xlUp).Row
    Dim url As String
    url = ActiveSheet.Range("CN" & i).Value
    Application.FollowHyperlink url

    ' Copy the adjacent value in column CM
    Dim valueToCopy As String
    valueToCopy = ActiveSheet.Range("CM" & i).Value
    ActiveSheet.Range("CM" & i).Copy

    MsgBox "Click OK to continue to the next URL", vbOKOnly
Next i

End Sub

 

[Michael M]

Try using this first, to make sure it works

Activeworkbook.FollowHyperlink address:=Range("CN" & i).value, NewWindow:=False, AddHistory:=True

 

[Knockoutpie]

Try using this first, to make sure it works

Activeworkbook.FollowHyperlink address:=Range("CN" & i).value, NewWindow:=False, AddHistory:=True

Unfortunately this comes up with the same error, cannot open the specified file type.
It is a hyperlink, there's no formula in the cell.

I did come accross something that works well.

The only issue with the code below is if I change Range("A1").Select to CO2 or CN2 where my URL's are, it no longer works..

So as long as my URLs are in A1 it works.

Option Explicit

Sub openurl()

Dim EdgeLocation As String
Dim MyURL As String
Dim i As Integer
Dim ws1 As Worksheet
Dim lastRow As Long

Range("A1").Select

lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set ws1 = Sheets("Sheet1")

EdgeLocation = "C:\Program Files (x86)\Microsoft\Edge\Application\msedge.exe" 'Location of Edge.exe in your PC

For i = 1 To lastRow

    ' Copy the adjacent value in column CM
    Dim valueToCopy As String
    valueToCopy = ActiveSheet.Range("CN" & i).Value
    ActiveSheet.Range("CN" & i).Copy

MyURL = ws1.Cells(i, 1)
    Shell (EdgeLocation & " -url -newtab " & MyURL)

   
    If MsgBox("Continue to the next URL?", vbOKCancel + vbQuestion) = vbOK Then

Else
'Statements to show before Exit the Process
'MsgBox "You have skipped the Process"

Exit Sub
End If

Next i

End Sub