Why does Excel now give me already existing name range warning on Copy Sheet?

up vote 1 down vote favorite

I've been working on a Microsoft Excel 2007 spreadsheet for several days. I'm working from a master template like sheet and copying it to a new sheet repeatedly. Up until today, this was happening with no issues. However, in the middle of today this suddenly changed and I do not know why. Now, whenever I try to copy a worksheet I get about ten dialogs, each one with a different name range object (shown below as 'XXXX') and I click yes for each one: A formula or sheet you want to move or copy contains the name 'XXXX', which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in destination sheet, click Yes. To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box. The name range objects refer to cells in the sheet. For example, E6 is called name range PRE on multiple sheets (and has been all along) and some of the formulas refer to PRE instead of $E$6. One of the 'XXXX' above is this PRE. These name ranges should only be resolved within the sheet within which they appear. This was not an issue before despite the same name range existing on multiple sheets before. I want to keep my name ranges. What could have changed in my spreadsheet to cause this change in behavior? I've gone back to prior sheets created this way and now they give the message too when copied. I tried a different computer and a different user and the same behavior is seen everywhere. I can only conclude something in the spreadsheet has changed. What could this be and how can I get back the old behavior whereby I can copy sheets with name ranges and not get any warnings? Looking in the Name Manager I see that the name ranges being complained about show twice, once as scope Template and again as scope Workbook. If I delete the scope Template ones the warning goes away on copy however, I get a bunch of #REF errors. If I delete the scope Workbook ones, all seems okay and the warnings on copy go away too, so perhaps this is the answer, but I'm nervous about what effect this deletion will have and wonder how the Workbook ones came into existence in the first place. Will it be safe to just delete the Workbook name manager scoped entries and how might these have come into existence without my knowing it to begin with? microsoft-excel-2007 share|improve this question edited Sep 17 '12 at 13:55 asked Sep 14 '12 at 20:31 WilliamKF 1,80073575

Did you ever find out why these Workbook-scope names were appearing? I'm seeing the exact same thing - I can fix it by deleting the Workbook names, but I'd be happier if I knew what caused them. –  Neil Vass Dec 17 '12 at 20:57 No, I just deleted per the answer I gave below. It would be nice to know why this occurs. –  WilliamKF Dec 18 '12 at 14:25 add comment

4 Answers

active oldest votes

up vote 1 down vote accepted	Open the Name Manager and find the named items being complained about and delete all those with scope Workbook and the issue with copying sheets goes away and the formulas remain intact. share|improve this answer answered Sep 14 '12 at 20:51 WilliamKF 1,80073575
	add comment

up vote 1 down vote

This is not an error, but a warning a conflict exists and offers a choice on what to do When you copy a range that includes reference to a (typically worksheet scope) named range, and that same name exists on the destination sheet, how is Excel to know which name you want to use in the result? As to whats changed, I suggest you review all the names in your workbook, especially for scope. Name Manager - Ctrl-F3 share|improve this answer answered Sep 14 '12 at 20:52 chris neilsen 2,747410

I've updated the question to use the word 'warning' instead of 'error', but my question remains. –  WilliamKF Sep 17 '12 at 13:55 add comment

up vote 0 down vote

Same problem here with 27 mystery named ranges. I used CTRL-F3 (as recommended in another answer) to view list of named ranges (which I had never set up!). There were many since I had been copying the worksheet tab many times and just clicking 'yes" to work around the message. I highlighted and deleted all the named ranges (one little screen at a time, couldn't select All). the next time I tried to copy the worksheet, the copy succeeded without the warning message. Be sure to copy your original file before trying this. share|improve this answer edited Jul 1 at 19:15 oKtosiTe 3,99751550 answered Jul 1 at 18:49 Tess 1

add comment

up vote 0 down vote

These names are mostly the result of a data download through a third-party API. The APIs typically use those names as placeholders for cell referencing. Once you have processed some data, these names are mostly left behind as a general user does not clean up the hidden (or very-hidden) sheets. The difficulty associated with these names is that they also do not show up in the NAMES box and therefore cannot be deleted through that option. One way that I go about it is to program it through a VBA script. Sample below Sub do_loop_names() Dim vJunkName As Name Debug.Print ThisWorkbook.Names.Count For Each vJunkName In ThisWorkbook.Names vJunkName.Delete Next vJunkName End Sub Do note this script will delete ALL names from the workbook, so if there are certain defined names you'd like to keep, please put in the exceptions in the code. If someone has a more efficient solution, please let me know. share|improve this answer answered Nov 4 at 8:47 Viquar 111

I know hidden sheets. But what are very hidden sheets? –  nixda Nov 4 at 9:31 @nixda You can hide or unhide sheets by right clicking on the sheet tabs as you know. You can do the same thing through VBA, and through VBA you have another option to make sheets very hidden which means they are not available to unhide when right clicking on the sheet tabs. –  Levi Nov 4 at 23:14 @Levi gave a good explanation. very hidden sheets are invisible on the workbook unless set visible through VBA. Hidden sheets can be viewed through the spreadsheet Hide / Unhide option but the veryhidden feature makes these sheets inaccessible unless through VBA. –  Viquar Nov 12 at 13:38 add comment