I use worksheet level ranges extensively and I usually do it so that I can
use the exact same name on multiple sheets and then use similar code to act
on each sheet using the named range. If you have worksheet level range and
a workbook level range with the same name, the worksheet range will take
precidence. I'd try to print out the range name and the refers to address.
I'd be willing to bet that you have range names in the source workbook that
look like this:

Sheet1!RangeName

instead of just

RangeName

HTH,
Barb Reinhardt



"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