Built Hyperlink does not work in some cases

[cuddling101]

I have a working subroutine that creates a set of hyperlinks in a 'table of contents'.

Unfortunately the hyperlink that is created is not working, in a few cases.

Context

Example reduced in size for clarity

Workbook with five tabs and the table of contents tab

Workbook worksheet names (names changed for confidentiality)

Jenkins
Douglas
Aberthorn
Mountjoy of Windsor
Higgins_NI

Private Sub Create_GSUTPF_Index()
    
    Dim wb As Workbook
    Dim ws() As Worksheet
    Dim rng As Range
    Dim rngNow As Range
    Dim rngNowName As String
    Dim rngNowLink As String
    Dim wsCount As Integer
    Dim wsColor() As String
    Dim wsPrime As Worksheet

    Dim I As Long
    
    Set wb = ThisWorkbook
    Set wsPrime = wb.Worksheets("Coverage_Statistics") 'Targets Coverage_Statistics for placement of Table of Contents
    
    Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
    
    wsCount = wb.Names("Main_Families_Count").RefersToRange.Value
        
    Set rng = wsPrime.Range("A1:B" & wsCount)
    ReDim ws(1 To wsCount)
    ReDim wsColor(1 To wsCount)
    
    For I = 1 To wsCount
    
        Set ws(I) = Worksheets(I)
        wsColor(I) = ws(I).Tab.Color
        Set rngNow = rng(I + 1, 1)
        rngNow.Interior.Color = wsColor(I)
[COLOR=#ff0000]         [B]rngNowName = ws(I).Name[/B][/COLOR]
        rngNow = ws(I).Name
        ActiveSheet.Hyperlinks.Add Anchor:=rngNow, Address:="", SubAddress:= _
          rngNowName & "!A2", TextToDisplay:=rngNow.Value
        rngNow.Font.ColorIndex = 0
        
    Next I
    
    Application.ScreenUpdating = True 'Restores screen update
    
End Sub

I tried concatenating CHR(34) either side of rngNowName but that produced an unusable hyperlink.

Problem

The hyperlinks for Jenkins, Douglas, Aberthorn, and Higgins_NI all work fine, as exampled below, for the case of 'Jenkins'. Unfortunately the hyperlink created for 'Mountjoy of Windsor' produces a Reference isn't valid error when I click on it.

Working generated hyperlink

file:///G:\(Old PC)\Family_History (G)\Jenkins Extended Family History\Genealogical_Service_Users_Tracing_Project_Families.xlsm - Jenkins!A2

Not Working generated hyperlink

file:///G:\(Old PC)\Family_History (G)\Jenkins Extended Family History\Genealogical_Service_Users_Tracing_Project_Families.xlsm - Mountjoy of Windsor!A2

I feel sure that it is something to do with inserting some single or double quotes somewhere and/or some square brackets, but I am just not sure what.

Please do not suggest changing tab names as that would involve too much work across multiple spreadsheets.

I look forward to receiving some advice, please.

With thanks in anticipation

Philip
Bendigo, Victoria
Australia

 

[cuddling101]

The solution turned out to be the inclusion of a couple of CHR(39) statements, which inserted single quote characters in the correct place.

        ActiveSheet.Hyperlinks.Add Anchor:=rngNow, Address:="", SubAddress:= _
          Chr(39) & rngNowName & Chr(39) & "!A2", TextToDisplay:=rngNow.Value