If you're saying you wanted a worksheet level name, you could also use:

with activesheet
.range(.range(strstart), .range(strend)).name _
= "'" & .name & "'!ResourceList"
end with



NevilleT wrote:

Thanks to you all for such a quick response. Actually the response from Don
and Alan both do the trick. I should have been using worksheet rather than
workbook but I had taken what was a complex part of another spreadsheet and
tried to simplify it into a single sheet with one function. I am using
variable addresses for the real application.

Thanks again guys.

"Alan" wrote:

If you are using direct cell references, this one line will do everything
your code is doing. Your code is more usefull when dealing with variable
ranges (with some additional code added).

Range("A1:A3").Name = "ResourceList"

or


ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:= _
"=Sheet1!R1C1:R3C1"


Alan


"The only dumb question is a question left unasked."


"NevilleT" wrote in message
...
I have an obscure problem. I have simplified it down to the code below. I
am trying to create a named range with VBA but the range name has inverted
commas around it so it will not work. For example, the code below creates
a
named range for ResourceList of:
="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted
commas)

"Sub test()
Dim strStart As String
Dim strEnd As String
Dim strAddress As String

strStart = "$A$1"
strEnd = "$A$3"

strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd
ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress
End Sub





--

Dave Peterson