Sheet you want to copy contains the name...

M

Michael31

When I copy a worksheet I get the repeated message box that the destination
workbook already contains the name "a", "aa", "prntoutline", "ssd", "q" and
"zz".

I have to select yes to each message box to allow the worksheet copy to
complete itself.

I do not have these names within the worksheet (chekced by
Insert>Name>Define).

How can I delete these nsmaes that do not seem to exist?

Thanks.
 
T

Tom Ogilvy

Names can be hidden, so Insert=>Name=>Define may not show them. In the
troublesome workbook make a blank sheet active, then go to the VBE
(Alt+F11), do insert=>Module and paste in the below code. Then do Alt+F11
to return to the worksheet and then do Tools=>Macro=>Macros, select
ShowNames and click run.

Sub ShowNames()
i = 1
for each nm in activeWorkbook.Names
cells(i,1).Value = nm.name
cells(i,2).Value = nm.RefersTo
cells(i,3).Value = nm.Visible
i = i + 1
Next
End Sub
 
M

Michael31

Thank-you very much Tom.

I added ActiveWorkbook.Names(nm.Name).Delete
to your macro and deleted the buggers- I had over 100 unknown and unused
names in the workbook.

All were invisible as per your detection routine and the name's references
made no sense. I must have created them sometime ago with some bad VB code.


Thank you again and happy Easter.


Michael
Canada
 
M

Mike

Thanks. I had the same problem, but I got an error with the macro. I deleted
the line where i=2 and added Michael31's line. It now works. I don't know
much about macros
but I suspect that the error was because the orignal sources were no longer
available.

Mike
 
Top