C
christian_spaceman
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'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