VBA to enter formula that uses a variable that pulls sheet name as part of the formula... variable has a "-" in the name causing unexpected result

[sshone1]

Hello I have the following code that pulls a sheet name into a variable and then uses that variable (sheet name) to write a formula into another cell. It works great, unless the original sheet name has a "-" in it. It will then open a explorer window asking me for a file...? Anyone have an idea of why I cannot have a "-" in the name?

cName = copyWs.Range("A" & Y)

copyWs.Range("B" & Y).Formula = "=IF(" & cName & "!U5=0,""""," & cName & "!U5)"

Works great if cName = AT14495_01_ASM

In the cell I get the correct formula...
=IF(AT14495_01_ASM!U5=0,"",AT14495_01_ASM!U5)

However if cName = AT14495-01-ASM

I get a dialouge box titled Update Values:ASM and it wants me to select a file?

I know that sheet names can have "-" in them and you can write the formula manually and it works fine... Why is the script having this limitation?

Any help would be great
Thanks
Steve

 

[Fluff]

Try

copyWs.Range("B" & Y).Formula = "=IF('" & cName & "'!U5=0,"""",'" & cName & "'!U5)"

 

[sshone1]

Thank you that worked. Why did that work? What is the difference between the two cases?

 

[Fluff]

If a sheet name contains spaces or - then it needs to have an apostrophe front & back.

 

[sshone1]

Good to know. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.