To define a name with local scope it must include the sheetname
(wrapped in single quotes followed by the exclamation character) in its
definition. You can programmatically assign local names as follows:

Range("$A$1").CurrentRegion.Name = "'" _
& ActiveSheet.Name & "'!" & sRngName

So if the sheet was named "Charting" and the value held in sRngName is
"Sales" then the above would read...

Range("$A$1").CurrentRegion.Name = "'Charting'!Sales"

...which is how you would define it in the Namebox or name field in the
Defined Names dialog if you manually selected the area and typed the
name.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc