Actually, the bit about it making a difference whether I hard code the
value or not may be complete rubbish! I've just tested again. I
basically don't understand why it's a worksheet level named range not
a workbook level one!

Cheers (and happy christmas!)

Chris




On 24 Dec, 12:40, christian_spaceman wrote:



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