7

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?

Improve this question
13
  • 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
    Commented Jan 12, 2012 at 15:06
  • Yes, two copies of the same sheet. Thanks for the Task Manager tip, that is exactly what I had to do.
    – Reinstate Monica - Goodbye SE
    Commented Jan 12, 2012 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
    Commented Jan 12, 2012 at 15:12
  • 2
    Are these things defined names on the workbook? If so, they will cause your error.
    – soandos
    Commented Jan 12, 2012 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
    Commented Jan 12, 2012 at 17:31

3 Answers 3

Reset to default
9

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.

Improve this answer
1
  • 1
    I wish I could upvote this 20 times.
    – Ivan X
    Commented Nov 4, 2016 at 14:23
2

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.

Improve this answer
3
  • @soandos - if you enter your own version of this answer, I will delete mine and accept yours.
    – Reinstate Monica - Goodbye SE
    Commented Jan 16, 2012 at 14:47
  • 1
    With me it only worked when I close and reopen Excel after deleting the entries.
    – Gerhard Powell
    Commented Apr 11, 2014 at 19:28
  • How do you get there since the menus are inaccessible ?
    – Overmind
    Commented Jan 5, 2018 at 6:08
0

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?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .