Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 24th 07, 12:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 25
Default Named ranges scope / workbook/worksheet level named ranges- changeswith variable use...

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

  #2   Report Post  
Old December 24th 07, 12:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 25
Default 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!)

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


  #3   Report Post  
Old December 24th 07, 12:55 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,355
Default Named ranges scope / workbook/worksheet level named ranges- change

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

  #4   Report Post  
Old December 24th 07, 01:15 PM posted to microsoft.public.excel.programming
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:

='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 -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting all named ranges that have a workbook scope Babymech Excel Discussion (Misc queries) 3 February 19th 09 04:21 PM
Copy worksheet with named ranges to new workbook and keep names Sandy Excel Worksheet Functions 0 July 11th 08 04:37 PM
Phantom named ranges in a workbook? Dave O Excel Discussion (Misc queries) 3 September 26th 06 05:26 PM
Copy data in named ranges to a newer version of the same template to identical ranges handstand Excel Programming 0 August 21st 06 03:51 PM
named ranges at workbook and worksheet levels mark kubicki Excel Programming 5 September 15th 04 10:46 PM


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017