copying an excel worksheet causes "name" error

[Derek Ruesch]

When I attempt to copy an Excel worksheet to a new
worksheet by going to Edit>Move or Copy Sheet..., I get
the following error message:

"A formula or sheet that you want to move or copy contains
the name 'what', which already exists on the destination
worksheet. Do you want to use this version of the name?
*To use the name as defined in the 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."

When I click "Yes", I get this dialog box several more
times with a different word following "contains the name".
The words following this phrase include error, error2,
what and who. After I have clicked "Yes" each time this
error message appears, then the data appears to have been
copied successfully.

Why am I getting these error messages? Why is the error
message saying a certain name already exists in the
destination worksheet, when the worksheet hasn't even been
created yet? Also, these names do not appear anywhere in
the worksheet that I am copying.

I am running Excel97 and this particular worksheet does
have a data link on it.

Please help! Thanks.

Derek Ruesch

 

[Dave Peterson]

You may want to post the specific names that excel yells about (was it really
WHAT???)

But maybe you have a template workbook that contains names (book.xlt in your
xlstart is where I'd start looking).

Then I'd use a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.co.uk/mvp

It makes working with names much easier. And I bet you'll be able to find the
culprit (and probably make the fix).

 

[Derek Ruesch]

Thanks. That name manager utilitiy worked.

I do have one other question though: What is a "hidden
name"? I am asking this because this name manager allows
you to show "hidden names". These "hidden names" were
causing the error so I deleted them.

Please respond. Thanks.

Derek

 

[Dave Peterson]

Hidden names are names that are not visible to the end user.

Excel creates some for its own use and you can can write code to create hidden
names (or hide existing names), too.

You can do this in a variety of ways:

Option Explicit
Sub testme()

ActiveSheet.Range("a1").Name = "Hi"
ActiveWorkbook.Names("HI").Visible = False

ActiveWorkbook.Names.Add Name:="ddd", _
RefersToR1C1:="=Sheet1!R9C6", Visible:=False

ActiveWorkbook.Names.Add Name:="topsecret", _
RefersTo:=True, Visible:=False

End Sub

But why? Well for one, it gives a little more flexibility for the programmer.
He or she could check a name to see if it's in the workbook. If it's there,
then they can continue (or not).

If that name is hidden, it makes it more difficult for the average user to find
it and destroy it.

(although with Jan Karel's (et al) utility, this goes away pretty quickly!)

===
You may want to be a little careful just killing those names.

Try this with a test worksheet.

Fill a sheet with values.
Apply Data|Filter|autofilter

Now run that name manager and you'll see a name that looks like:
Sheet1!_FilterDatabase
(excel created it)

Now use the name manager to delete that name.

Back to the worksheet and try using the autofilter. Deleting the name broke the
filter, but didn't remove the arrows.

You can break things, so be a little(?) careful.