• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why do named ranges propagate when I copy a sheet? [SOLVED]

dronka

dronka

Member
I'm using named ranges within my workbook. One of my sheets (Sheet 1) has about 5 named ranges. Other sheets in my workbook refer to those ranges. But when I copy sheets that have nothing to do with the named ranges (e.g. sheets that do not refer to the ranges), I get a bunch of messages that tell me the sheet I'm copying contains a named range that already exists on the destination sheet, and asks whether I want to use that version of the name.


When I go to the name manager, I see multiple instances of each name. It seems that each time I copy a sheet, it creates a new instance of each of the named ranges on Sheet 1. The "Scope" column of the name manager lists a bunch of different sheets (including "Workbook").


Note: I am not copying the sheet that has the original name ranges on it. I'm copying other sheets.


Any idea why this is happening?
 
I would check the scope of the ranges you set up. As you saw, there is a difference between a worksheet scope and workbook scope. All the sheets have access to named ranges with workbook scope, so that is why you're seeing the message when you copy, because XL doesn't know what scope you want.


I would recommend changing the scope on the ranges to workbook.


Note, I'm also assuming that all this copying/pasting is within the same workbook, not to/from other workbooks.
 
dronka - Sometimes this is exactly what you want - particularly in the case that you have set up names with a scope limited to a particular sheet, and you have VBA code that says something like:


With activesheet

IF Range("SomeLocalName") = blah

'do something

End If

End With


But I agree that that message can be pretty mysterious, and it's tedious that there's no 'yes to all' option, meaning you sometimes have to click the OK button a squillion times. And pushing the cancel button doesn't cancel, from memory.
 
You know...this error is still a bit mysterious to me. I've recieved it many times over the years, but I'm damned if I can reproduce it now on purpose. Tried setting up global names on sheets in different workbooks, then copying one sheet to the other workbook. Got no error. Tried setting up a global name in one workbook, and making a copy of the sheet in the same workbook. No error.


Anyone else able to reproduce it on purpose, so I can study this further?
 
Hi, dronka!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

PS: Please detail the specific steps that lead you to the error.
 
@jeffrey


This worked. Probably can do it in less steps, but it generates the error.


In workbook1

1. On sheet1, give a name "Tom" to A1:A10

2. On sheet2, give a name "Jerry" to B1:B10

In workbook2

1. On sheet1, give a name "Jerry" to C1:C10

2. On sheet2, give a name "Tom" to D1:D10

Select sheet1 and sheet2 of Workbook2, try and copy to workbook1. Should throw an error.
 
@Luke M

Hi!

Nice catch. And it fails too even if worksheet names are different between both workbooks.

Regards!
 
Looks like others have this issue, too...


All of my worksheets are in the same workbook.


I see where I can change the scope (if I go to the range, highlight it, and click on Define Name, I'm able to change the scope). But what impact does that have? -- if I change the cope from Workbook to the specific sheet where the range resides, for example.
 
I take it back. I'm having difficulty changing the scope in my names that are currently scoped as "Workbook." When I edit the name under Name Manager, Scope is grayed out. And when I go to the range that it refers to and click Define Name, it looks like it wants me to set up a new name.


And I'm still wondering what difference it makes that a scope is Workbook or the specific sheet.


Thanks!
 
...And is there a way to easily find out where a named range is being used (equations, VBA, etc.)? I know I can do a CTRL-F to search for the name. Is that the only way? I want to delete some names that I think aren't being used any more, but I don't want to mess things up.
 
Dronka - download Name Manager from this link: http://www.jkp-ads.com/officemarketplacenm-en.asp

It's awesome, and not only lets you change scope but also has a 'show names' option that highlights where names are used in the workbook.
 
Hi, dronka!


It's an Excel issue or feature still not available. The scope of named ranges is set at creation time and can't be modified, that's why you get the grayed dropdown. There are alternatives like using Add-ins, for example Name Manager from Jan Karel Pieterse & Charles Williams & Matthew Henson:

http://www.jkp-ads.com/officemarketplacenm-en.asp


The manual alternative is to copy the definition, delete the range, create a new one with the same name and different scope and paste the definition.


Regards!


@jeffreyweir

Hi!

You weren't here when I begin this post, and I wrote longer... you should toot next time :)

Regards!
 
Hi ,


For more details on scopes of named ranges , check out these links :


1. http://www.myonlinetraininghub.com/excel-2007-named-ranges-explained


2. http://www.accountingweb.com/topic/technology/everything-you-need-know-about-excel-range-names


3. http://www.cpearson.com/excel/DefinedNames.aspx


4. http://www.sumproduct.com/thought/names


Narayan
 
I downloaded the http://www.jkp-ads.com/officemarketplacenm-en.asp name manager. Very cool application! HOWEVER, when I used the button that looks up all references to the name, it showed no references to the name when the name was actually referenced in VBA code. Strange. I ended up deleting a lot of names that were actually referenced in VBA and screwed up my code. Luckily I had saved. :)


So what I'm doing now is using the program to convert global names to local. But I'm finding that after conversion I have to change the name in the VBA code to include the sheet name; e.g. AmountDue as a global name becomes Sheet1!AmountDue when converted to a local name on Sheet 1. And I have to update the VBA code accordingly.


When I do this, the name no longer gets propogated when I copy sheets in the workbook. That solves the original problem highlighted in this post.


But what is the downside (if any) of converting global names to local names? Could there be an impact that I don't yet see?
 
Did some research as Narayank991 suggested. Looks like global names can be referenced on any other sheet and local names can only be referenced on the sheet where the name is.


I also learned that when you name a range on a table, that name will propagate when you copy sheets. So I updated my code to reference the table (e.g. ("TableName[ColumnName]") rather than using a named range. When I deleted the named ranges, the problem of propagation was solved, and my VBA code still worked.


I also found that a global named range that references a cell that is NOT in a table does NOT propagate when sheets are copied.


Finally, although the name manager referenced above is very cool(http://www.jkp-ads.com/officemarketplacenm-en.asp) and extremely helpful, BEWARE: when you use the feature that looks for where a name is used, it will not register when the name is being used in VBA code. To determine that, you need to go into your VBA project and search on

the name using CTRL-F (search the whole project). I deleted a name that was being used in VBA because that was the only place it was being used and the name manager didn't show it as being used. That said, it's a pretty awesome application.


Thanks everyone for helping me with this issue!
 
Glad you got it figured out, and that we could all learn a little bit about ranges/scoping. =)
 
@Dronka

Looks like global names can be referenced on any other sheet and local names can only be referenced on the sheet where the name is. Local names can be referenced from any sheet, provided you preceed the name with the sheetname that the name exists in. i.e. if you have a local name called test on Sheet1, then from any other sheet you can reference it using Sheet1!test.


I also learned that when you name a range on a table, that name will propagate when you copy sheets. This happens irrespective of whether your name is in a table or not. Basically, if you have some globally defined names on a sheet and you copy that sheet within the same workbook, Excel will make local names out of the global names for that sheet.


So if we have the global name ‘test2’ on Sheet1, and we copy the entire sheet, then Excel adds a new locally scoped name to the new sheet. You can see this in the name manager. And again, you can reference this name from any sheet by prefixing the local name with the sheetname. i.e. from Sheet1 we would use this:

'Sheet1 (2)'!test2


Furthermore, try this. Define a global name “test3” in a sheet, then define a local name “test3” in the same sheet. Now go to a new cell in that sheet and type =test3. You’ll see that a picklist will appear where you have to choose whether you want the global name or the local name.

Now go to another sheet and type test3. You’ll see that only the global name is available.


BEWARE: when you use the feature that looks for where a name is used, it will not register when the name is being used in VBA code.

That’s not my experience, and it should work. According to the website Rename a name. Name Manager includes a rename option, which will enable you to change the name of a name and have all objects that use that name update too. (this includes VBA).


Maybe it’s something specific to how you are referencing the name in VBA? I.e. are you building the name up from a string? (not sure if this makes a difference or not, just guessing). Or maybe the app just bombed out.


I’d be interested to know if I can replicate this. Also you can post a question or issue at http://www.jkp-ads.com/officemarketplacenm-en.asp and Jan Karel is very helpful in troubleshooting. In fact I note there’s a question there along the lines of “When I change the status of a name from global to local-to-a-sheet, formulas that used the global names are not updated accordingly” that might be the same issue you are having, as well as a response from Jan Karel.
 
@James – thanks for the example of how to bring up the conflict dialog box. I think in that particular case the only reason the dialog is coming up is because Excel hasn’t been programmed to handle renaming names during a multiple sheet copy. To see what I mean, also note that:

1. if both names are in the same sheet, and you create a copy of that one sheet, then you don’t get the error.

2. If both names are in the same sheet, and you select that sheet and another sheet with no names whatsoever, you do get the error.


By virtue of this discussion, I also found why that dialog often comes up for me…it’s in the case where you have inadvertently defined both a globally defined name ‘somename’ AND and a locally defined name ‘somename’ on the same sheet, and you copy the sheet. (I often accidentally end up with both local AND global, due to the fact that I sometimes accidentally create a global name when I actually wanted a local one, but then when I create that local one I forget to delete the global one.)


Consequently when you copy the sheet – along with the two names - Excel can’t convert them both to local names because they would have exactly the same name i.e. ‘Sheet1 (copy)’!somename


To illustrate, define a global name “test3” in a sheet, then define a local name “test3” in the same sheet. Now try to copy that sheet.
 
@All - I've managed to reproduce something truly bizarre. And it's definitely a bug.


Try this: Define a named range on Sheet1 called 'test'. In sheet 2, put =test into any cell (but don't define ANY names in Sheet2). Don't put anything at all in Sheet3.


Now select Sheet2 and Sheet3, and try to make a copy. You get the error message 'A formula or sheet you want to move or copy contains the name 'test', which already exists on the destination sheet. Do you want to use this version of the name?'


What the...there are NO names in the source OR destination sheets!


Even weirder, click NO, and put a new name 'test2' in the Name Conflict dialog box that comes up. Then go to the Name Manager and see where than name got created, and with what scope. What the...it got created in the original sheet (Sheet1) with global scope...even though we were not doing anything at all with that sheet.


Just the mere fact that they are being copied together, and that one of them refers to a named range elsewhere - is enough to cause this weird behavior.
 
Truly an odd item there Jeffrey...will have to watch out for this in the future.
 
With regards to odd items

Excel also doesn't like the use of "Chart" as part of a named formula name, when being used in a chart


Cht works great
 
You must log in or register to reply here.
Back
Top