Put a stop in your code just before naming the range like the following then
while it is stopped and before cancelling the process, rest your cursor over
the variables varName and then varStartCell and see what value VBA has
assigned to them.

varName = "MyNamedRange" 'Just as demo. No doubt your code is different
varStartCell = "$A$5"

Stop

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="Sheet!" & varStartCell


If you can't fix then post a bit more of your code showing how varName and
varStartCell are populated and manipulated. ie stipped of blanks etc.

--
Regards,

OssieMac


"headly" wrote:

Actually, no, I get a name like ="sheet1!$A$5" but no idea where the "" came
from

"Gary''s Student" wrote:

You are missing an equals sign:

Sub Macro1()
Dim varName As String
Dim varStartCell As String
varName = "alpha"
varStartCell = "$A$5"
ActiveWorkbook.Names.Add Name:=varName, RefersTo:="=Sheet!" & varStartCell
End Sub
--
Gary''s Student - gsnu200796


"headly" wrote:

Code looks like this:

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="Sheet!" & varStartCell


Creates a name, which I cannot use, because it is surrounded by quotation "
" symbols.
Note about Varname: is created after stripping out spaces from user entered
input; varStartCell is an absolute reference like $A$5

TY