juan4412
12-07-2016, 10:14 AM
I am trying to create hyperlinks that link to worksheets that have names in column B, but add the hyperlink to column O. This is my syntax, but it produces an error of
Invalid Procedure Call Or Argument on the ActiveSheet line.
Function CreateHyperlink()
Dim lr As Long, i As Long, ws As Worksheet
Set ws = ActiveSheet
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
If ws.Cells(i, 2).Value Like "*,*" Or ws.Cells(i, 2).Value Like "*'*" Then
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:="'" & ws.Cells(i, 2).Value & "'!A1", TextToDisplay:=ws.Cells(i, 15)
Else
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value & "!A1", TextToDisplay:=ws.Cells(i, 15)
End If
Next i
ws.Activate
End Function
Invalid Procedure Call Or Argument on the ActiveSheet line.
Function CreateHyperlink()
Dim lr As Long, i As Long, ws As Worksheet
Set ws = ActiveSheet
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
If ws.Cells(i, 2).Value Like "*,*" Or ws.Cells(i, 2).Value Like "*'*" Then
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:="'" & ws.Cells(i, 2).Value & "'!A1", TextToDisplay:=ws.Cells(i, 15)
Else
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value & "!A1", TextToDisplay:=ws.Cells(i, 15)
End If
Next i
ws.Activate
End Function