View Single Post
  #4   Report Post  
Old December 24th 07, 01:15 PM posted to microsoft.public.excel.programming
christian_spaceman christian_spaceman is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2007
Posts: 25
Default Named ranges scope / workbook/worksheet level named ranges-change

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



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!



On 24 Dec, 12:55, Barb Reinhardt
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: *


instead of just


Barb Reinhardt

"christian_spaceman" wrote:

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


Chris- Hide quoted text -

- Show quoted text -