Named ranges scope / workbook/worksheet level named ranges-changes with variable use...
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!)
On 24 Dec, 12:40, 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