VBA Making Me Update Values When Using Values with Spaces

[CtrlAltRage]

I have a spreadsheet that creates new sheets when a new value is entered in the first sheet. It then make the value that was entered in the first sheet clickable so when I select it - it automatically takes me to the newly created sheet.

For some reason - the only way I can get it to work is if I do not use spaces for the values I enter in.

Otherwise I get this prompt:

The script is below:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then
        Sheets("Project Template").Copy after:=Sheets("PROJECTS")
        ActiveSheet.Name = Target.Value
        ActiveSheet.[A1] = ActiveSheet.Name
    End If
    Me.Activate
    Target.Offset(, 2).Formula = "=" & Target.Value & "!D1"
    Target.Offset(, 3).Formula = "=" & Target.Value & "!G13"
    
End Sub
    'ADDED
    '--------------------------------------------------------------------
Private Sub Worksheet_selectionChange(ByVal Target As Range)
    If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Evaluate("=ISREF(" & Target.Value & "!A1)") Then Exit Sub
    Sheets(Target.Value).Activate
End Sub

Any and all advice would be greatly appreciated, thanks!

 

[Z51]

When referencing sheet names with spaces, you need to add single apostrophes like so:

    Target.Offset(, 2).Formula = "='" & Target.Value & "'!D1"
    Target.Offset(, 3).Formula = "='" & Target.Value & "'!G13"

(Between the = and " signs, and between the " and ! signs.)

 

[CtrlAltRage]

When referencing sheet names with spaces, you need to add single apostrophes like so:

    Target.Offset(, 2).Formula = "='" & Target.Value & "'!D1"
    Target.Offset(, 3).Formula = "='" & Target.Value & "'!G13"

(Between the = and " signs, and between the " and ! signs.)

You have saved me so much frustration - thank you! It's amazing how that one simple fix alleviated all the time I was spending trying to figure it out.

The only thing I will say though - when I update the script with the ' between the =" and "! the cell doesn't take me to the target sheet when I click on it. Am I missing something?

 

[Z51]

You have to add the apostrophes in the SelectionChange event, too...

If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub

 

[CtrlAltRage]

You have to add the apostrophes in the SelectionChange event, too...

If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub

You are a wonderful person haha

Thank you so much for the help!

 

[Z51]

While you're at it, you may just want to add them to the Evaluate line in the Worksheet_Change event, too.

If Not Evaluate("=ISREF('" & Target.Value & "'!A1)") Then