Board index » Excel » copying an excel worksheet causes "name" error

Excel4
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


-

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 wrote:

--



Dave Peterson

ec35720@msn.com

-

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

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.



Derek Ruesch wrote:

--



Dave Peterson

ec35720@msn.com

-