Hi,

I'm writing some vba for a sheet which makes extensive use of named
ranges. For one reason or another, I want to copy over some _more_
named ranges from another workbook (i.e. their position and names).

I've got the following setup:

Dim sourceWorkbook As Workbook
Dim destWorkbook As Workbook

Set destWorkbook = ThisWorkbook
Workbooks.Open ("C:\...blah blah blah.xls")
Set sourceWorkbook = ActiveWorkbook

The sourceworkbook contains the named ranges that I want to copy over.
Both workbooks have the same sheet names and layouts... I only want to
copy over the named ranges from a given sheet which is common to both
sheets (lets call it the 'curves' sheet).

I loop through the source's named ranges, and if the range occurs on
the 'curves' sheet, then copy it over...

For Each nn In sourceWorkbook.Names


sourceName = nn.Name
sourceRange = nn.RefersTo

destworkbook.Names.Add Name:=sourceName, RefersTo:=sourceRange
Next nn

This half works. It copies the name so that the name is accessible on
the 'curves' sheet but not on any other sheets with within the
destworkbook. I believe this is because it is set at the wrong level -
worksheet as oppposed to workbook as other sheets acn access the range
if they do worksheet_name!named_range_name.

What is more vexing is the fact that if I hard code the values they
are accessible to the entire workbook. i.e.

destworkbook.Names.Add Name:="Sims", RefersTo:="='curves'!$A$!"


I don't understand the difference - why one of these produces a
workbook level named range and the other a worksheet level! Any help
would be very greatfully received

Cheers,

Chris