Hyperlink help

[mc12997]

I am new to the forum and new to coding so please forgive my inexperience!

I have the following code (I didn't create it) that searches for a worksheet with the same name as the list of sheet names and creates a hyper link to the worksheet where the name is found. The worksheets are already created from template and named based on the same names but from a different sheet than the sheet referenced below (FY20 Key Initiative Calendar) and a different list but the same information. When the worksheets are created from the template and the other list of names, the names are sometimes larger than allowed for a worksheet name and some may have invalid characters as well. When the code creates the worksheets it uses code to make a valid name. The below code works well if the name matches the worksheet name exactly but if the worksheet name is shortened or changed in any way, it will not create the hyperlink. I could use some help modifying this code to search for the first 31 characters of the names in the list and ignore any special characters (they will be valid characters but potentially changed from what they are in the list).

Sub ADMIN_HYPERS()'==========================================
'DEFINE (& ASSIGN) VARIABLES
'==========================================
Dim s1 As String: s1 = "FY20 Key Initiative Calendar" 'name of sheet to search
Dim co_1 As Integer: co_1 = 2 'column containing values to assess
Dim rw_1 As Long: rw_1 = 15 'first row in range containing possible hyperlink
Dim rw_2 As Long: rw_2 = Sheets(s1).UsedRange.Rows.Count
Dim h_val As Variant
Dim e As Variant




'==========================================
'LOOP DEPTS & ADD LINKS
'==========================================
Do Until rw_1 > rw_2
h_val = CStr(Sheets(s1).Cells(rw_1, co_1))
On Error GoTo Handler:
e = Sheets(h_val).Cells(1, 1)
Select Case CStr(e)
Case "1"
'do nothing -- error
Case Else
Sheets(s1).Hyperlinks.Add Anchor:=Sheets(s1).Cells(rw_1, co_1), _
Address:="", _
SubAddress:="'" & h_val & "'!A1", _
TextToDisplay:=h_val
End Select
e = 0
On Error GoTo 0
rw_1 = rw_1 + 1
Loop
'==========================================
'END
'==========================================
Exit Sub
Handler:
e = 1
Resume Next
End Sub




Function GET_COL(s1 As String, crit As Variant, Rw As Long, m_ord As Integer)
GET_COL = Application.WorksheetFunction.Match(crit, Sheets(s1).Rows(Rw), m_ord)
End Function




Function GET_ROW(s1 As String, crit As Variant, co As Integer, m_ord As Integer)
GET_ROW = Application.WorksheetFunction.Match(crit, Sheets(s1).Columns(co), m_ord)
End Function

Thank you in advance for any help and suggestions!

 

[Yongle]

When the worksheets are created from the template and the other list of names, the names are sometimes larger than allowed for a worksheet name and some may have invalid characters as well. When the code creates the worksheets it uses code to make a valid name.

Your suggestion is a workaround which may cause other issues later
Eliminate the problem by correcting the (bad) "list of sheet names"
- use the existing code that is used to make a valid name to correct the bad list