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

soroush.kalantari · #1 07-31-2023, 08:02 PM

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 · #2 08-01-2023, 02:32 AM

Change to:

Code:

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

but you could try this instead:

Code:

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 · #3 08-01-2023, 11:01 PM

Quote:

Originally Posted by p45cal

Change to:

Code:

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

but you could try this instead:

Code:

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Merging 2 different cells containing IF formula & change in cell values based on multiple time frame	jay_excel	Excel	0	07-29-2017 11:04 PM
copy cells from a worksheet into other worksheets based on Criteria	Elton Wolter	Excel Programming	4	04-16-2016 08:44 AM
Column B Cell 25 of all worksheets copies data found in the lower cells ...	rpcalo	Excel	3	03-10-2016 03:38 AM
Reference another workbook and return values from the same cell across multiple sheets/tabs	lwls	Excel Programming	5	11-08-2014 02:11 PM
Change values in cells based on criteria	SaneMan	Excel Programming	2	02-02-2012 07:58 AM