Error when hyperlinking Excel cells to worksheets based on the cell values for sheets whose name in

[soroush.kalantari]

I have written following macro to hyperlink Excel cells to worksheets based on the cell value. It failes to work for sheets whose name include “-“. (See cell A4 in the excel attachment.)
Can you guide me on this issue?




Sub FShyeperlink()


On Error Resume Next

Dim n As Integer
Dim i As Integer
Dim rng As Range
n = Selection.Rows.Count
Set rng = Selection
Selection.Hyperlinks.Delete
For i = 1 To n


rng(i).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Selection.Value & "!A1"
Next i
End Sub

[p45cal]

Change to:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & Selection.Value & "'!A1"

but you could try this instead:

Sub FShyeperlink()
Dim cll As Range
'On Error Resume Next' only use this if you need it.
For Each cll In Selection.Cells
  cll.Hyperlinks.Delete
  cll.Parent.Hyperlinks.Add Anchor:=cll, Address:="", SubAddress:="'" & cll.Value & "'!A1"
Next cll
End Sub

[soroush.kalantari]

Originally Posted by p45cal

Change to:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & Selection.Value & "'!A1"

but you could try this instead:

Sub FShyeperlink()
Dim cll As Range
'On Error Resume Next' only use this if you need it.
For Each cll In Selection.Cells
  cll.Hyperlinks.Delete
  cll.Parent.Hyperlinks.Add Anchor:=cll, Address:="", SubAddress:="'" & cll.Value & "'!A1"
Next cll
End Sub

Thank you very much. Your reply solved the problem and learned me some new things about making my codes more concise and optimum.