Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When I want to copy a sheet (within the same file, so simply using left mouse button and Ctrl) in Excel 2007, I get this pop up dialog:

Yes / No dialog

where Author is a specific field somewhere in the sheet I'm editing.

The catch is that I cannot get rid of this dialog. If I press Yes, I get the same dialog with a different field. I have no idea how many fields there are in this sheet, but these dialogs just keep popping up...

If I press No, I get:

Name conflict

If I press Cancel (I don't even know what I'm changing, so I don't want to change it!), I get:

Name not valid

even though I pressed Cancel! (Same happens if I press OK without entering anything. If I enter something, I go back to the first dialog with the next field.)

Since I want to get out of this, I use Task Manager to quit Excel and then get:

Cannot quit Excel!

To prevent me banging my head on the table, can anyone tell me how I can change the Excel sheet so that these pop ups stop occurring?

share|improve this question
    
What do you actually want to accomplish? Two copies of the same sheet in the same workbook? (to close excel, use task manager->end process for excel.exe) – soandos Jan 12 '12 at 15:06
    
Yes, two copies of the same sheet. Thanks for the Task Manager tip, that is exactly what I had to do. – Wikis Jan 12 '12 at 15:10
    
And so it's clear, you right clicked on the sheet tab, clicked move or copy, checked the create a copy box, picked your location, hit OK, and got these errors? – soandos Jan 12 '12 at 15:12
2  
Are these things defined names on the workbook? If so, they will cause your error. – soandos Jan 12 '12 at 15:28
2  
The best way to handle the first message box is to click Yes. You will get that message for every similarly-defined & used name... Use the Name Manager to see how many named ranges you have. – Rachel Hettinger Jan 12 '12 at 17:31
up vote 1 down vote accepted

Go to Formulas -> Name Manager, select the whole list in the pop up box and press Delete.

You may also need to close and re-open the file to get rid of these messages.

share|improve this answer
    
@soandos - if you enter your own version of this answer, I will delete mine and accept yours. – Wikis Jan 16 '12 at 14:47
1  
With me it only worked when I close and reopen Excel after deleting the entries. – Gerhard Powell Apr 11 '14 at 19:28

This error generally arises when there are some erroneous names in the worksheet. Press Ctrl+F3 (the Excel name manager box will show up). On the right hand side there will be a filter button - select "Names with error" and once all of them show up, delete the erroneous names.

Now try to copy the sheet.

Does it work?

share|improve this answer

I had the same problem, but Name Manager does not reveal all the names embedded in the sheet. Somehow I got a bunch of hidden names in the worksheet.

Here is how to unhide them

Sub ShowNames()  
  Dim nmName As Name  
  For Each nmName In ActiveWorkbook.Names   
    nmName.Visible = True  
  Next nmName  
End Sub  

Now, you can see again all names in Name Manager and delete them.

share|improve this answer

protected by Michael Kjörling Mar 31 at 15:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.