How do I get rid of name conflict dialog box in Excel?

This is the dialog box that is displayed when you copy a worksheet into a workbook that already contains those named ranges.
Excel automatically converts workbook level named ranges to worksheet level named ranges without any prompts.
This automatic conversion obviously causes a lot of confusion.

Workbook Level Named Ranges

If your existing workbook and the worksheet you are importing both contain the same workbook level named range, the workbook level named range from the worksheet you are inserting is automatically converted to a worksheet level named range.
Lets imagine you have two identical workbooks both containing a single worksheet with the same workbook level named range.
Open both the workbooks and move one worksheet into the other workbook.
You will notice that the worksheet is inserted without any problems.
However on further investigation you will see that the named range on the worksheet you have inserted has become a worksheet level named range.
If you import a worksheet (with named ranges) into a workbook (with no named ranges) then the named ranges are copied across as you would expect.

Worksheet Level Named Ranges

If the worksheet you are inserting contains a worksheet level named range that already exists then Excel will display ???
Lets imagine you have two identical workbooks both containing a worksheet level named range and workbook level named range with the same name.
The worksheet "Sheet" contains two named ranges.

This is basically a choice between using the name already defined in the workbook or allowing you to type in a new named range.
Yes - Pressing Yes will use the named range that is defined in the destination workbook.
No - Pressing No will display the Name Conflict dialog box and allow you to rename the named range to something else.

If there is a workbook level named range already with the same name ?
The option exists to change the names in the source formula before pasting into the new workbook.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext

1. 09-19-2018, 11:02 PM #1

   

   Valued Forum Contributor

   

   ---

   Best way to quit the Name Conflict loop in Excel 2016?

   Does anyone know of a way to get out of this besides CTRL-ALT-DEL and stop the Excel process?

   Somebody had this error and that's what they did with Excel 2007 (https://superuser.com/questions/3776...he-name-dialog), and that's what I had to do tonight with Excel 2016.

   The way the dialog boxes go, even when you X, or cancel, or no, it won't let you stop the copy-and-paste operation, so you have to hard-stop Excel to move on.

   I appreciate any help.

   Regards,
   leaning

   ---

2. 09-20-2018, 10:37 AM #2

   Re: Best way to quit the Name Conflict loop in Excel 2016?

   The messages are a consequence of copying and pasting cells that have named ranges on them. So the first question is, when you do this do you want the pasted cells to use the original name, or do you want them to add the duplicate name? You probably just want to say Yes to each one. You will get a prompt for each name--how many names do you have defined? What do you want to happen if you are trying to paste a name that already exists?

   If you want to prevent the prompt, I think you would need a macro that suppresses the prompts. I have not tried that before, but I think it is possible.

   Jeff
   | | |?| |?| |?| |?| | |:| | |?| |?|
   Read the rules
   Use code tags to [code]enclose your code![/code]

   ---

3. 09-20-2018, 01:47 PM #3

   

   Valued Forum Contributor

   

   ---

   Re: Best way to quit the Name Conflict loop in Excel 2016?

   AM6StringJazzer,

   As it is, it's like "You are already using that name. Please enter a new name or we are going to overwrite that name with this duplicated names data."

   Hmmm. What I'd really like to do is cancel this copy-and-paste operation and take a look at the Name Manager to see what's going on, but it doesn't let you. The dialog boxes keep the focus so you can't click on anything on the ribbon. I don't want to overwrite, and I'd rather not give it a name just so I can go back in later through the Name Manager and compare them.

   So, since it won't let you back out, you have to C-A-D and hope that your autosave got all your recent work.

   The fact that even two users feel they have to crash the program to get past this means that it's an area for improvement, I think.

   Regards,
   leaning

   ---

4. 09-20-2018, 02:00 PM #4

   Re: Best way to quit the Name Conflict loop in Excel 2016?

   I would agree it's an area for improvement. I see the problem, although I have always just continued and then checked over the names afterwards.

   To be clear, it is not overwriting data. Suppose you have a sheet with a named range TestMe and you have a formula that says something like =TestMe*2. If you paste this sheet into a file that already has a name TestMe, it is telling you that it the name exists and you can either have the formula refer to the name that's already in the file, or you can provide a new name for what you are pasting in.

   ---

How do I remove a name conflict box in Excel?

How do I remove a name conflict?

How do you remove name errors in Excel?

How do I escape the dialog box in Excel?