Naming Sheets & Creating Hyperlinks-CodePudding

So I am creating a new sheet thusly:

Dim Naming As String
Dim ProjectNumber As String
Dim ws As Worksheet

ThisWorkbook.Sheets("Project Template").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet

Naming = CallLaunched.Value & " - " & ProjectLead.Value

ws.Name = Naming

This gives me a Sheet called something like: "May-21 - Sam Smith"

Later on at the end of the code I want to hyperlink to that new sheet from another sheet so I use this:

ActiveSheet.Hyperlinks.Add Anchor:=ws.Range("A" & LastRow), Address:="", SubAddress:=Naming & "!A1", TextToDisplay:=ProjectNumber

It all works, but when I click on the hyperlink I get "Reference isn't valid".

I cannot for the life of me figure out why the hyperlink isn't referencing my new sheet.

Any help would be appreciated,

Thanks,

Suzie

CodePudding user response：

Sheet names with spaces need quotes:

SubAddress:= "'" & Naming & "'!A1"