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

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

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

hmm.. i think you'd win that bet Barb...printing out sourceRange and
sourceName give the following sort fo thing:

='curves'!$J$200

A_10yr_Ch

Somthing you said really helped to solve this problem, though the
issue was different to what I originally thought. The 'curves' sheet I
had copied in from another workbook. I had originally thought that the
named ranges it already had weren't coming with it (hence my trying to
replace them). Turns out they were coming, but only as worksheet level
names - then the precedence issue you mentioned was having an effect.

I'll just adjust my code a bit, deleting the named ranges on the
'curves' sheet, then putting htem back in again - effectively
'promoting' them to workbook names.

Thanks for your help - I've spent hours looking at this!

Cheers,

Chris




On 24 Dec, 12:55, Barb Reinhardt
wrote:
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- Hide quoted text -

- Show quoted text -