You may also want to develop the habit of always sticking in a single
quote around the workbook/sheet name. That ensures your code works
even if the name contains a character such as a space.

ActiveWorkbook.Names.Add Name:="'sheet 1'!aName2", RefersToR1C1:= _
"='Sheet 1'!R5C1:R7C1"

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <rvExcelNewTip.1pqiad_1117271115.0853@excelforum-
nospam.com,
says...

Andy, John: I defined Names local to Sheet2 to reference the Data and
then used these qualified names to modify the Series for the chart in
Sheet1.

The problem I had was finally a tiny (!) programming error, but an
error indeed.

As I was working with dynamic Ranges, I had to Calculate their extents
(with the Address function). Then I added the Named Ranges to the
Names Collection:
.. -RefersTo:= strSheet2Name & "!" & Address(rngData)-

Those in the know spot the error immediately: I forgot the leading
equals sign.
.. -RefersTo.= "=" & strSheet2Name & "!" & Address(rngData)-

Which indeed resulted in a wrong formula for the Series.

PS. In the meantime I discovered the Name property of a Range which
does the same but with a more elegant syntax.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=373952