Avatar of murphji

asked on 

Copy worksheet - lots of names already exist

Have a macro that makes copies of worksheets, something like a base worksheet copied for several regions. The worksheet is copied and renamed in a macro. Essentially
Copy Base Sheet (new name is something like "Base1"
Rename Base1 to Region1
Repeat for other regions

When the copy worksheet executes I get about 60 "A formula or sheet you want to move or copy contains the name '<lots of wierd names>' which already exists on the destination worksheet.

I do not see any of these names in the Name Manager.

____thinkcellM0YAAAAAAAAAAA9 (it goes on with random-looking stuff
Several other thinkcell names
Several others containing 123Graph

These name warning/errors occur executing this statement:
    Sheets(sSourceSheet).Copy after:=Sheets(asAfterSheet)

Really odd, these same errors occur copying different source sheets. That is, I can copy any of several different sheets in the workbook and get the same bunch of name errors.

What are these names, and how do I get rid of them?

Microsoft Excel

Avatar of undefined
Last Comment
Avatar of andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

The names must be there somewhere! If you could post the spreadsheet with the macros and the base sheet we may be able to track them down.
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Rob Brockett
Rob Brockett
Flag of New Zealand image

hi guys,

It looks like Zorvek has gone to a lot of effort to give a detailed answer, but since his code has similar functionality to some of the options included in JKP's Name Manager I thought it worth providing a link to an alternative "finished product". The JKP Name Manager is a very useful and free excel addin, collaboratively created by Jan Karel Pieterse (JKP), Charles Williams, (www.decisionmodels.com) and Matthew Henson (mhenson@mac.com) and can be downloaded from: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp?AllComments=True

I use it in my work almost every day and it is regularly updated as bugs or optimisations are identified so I occasionally compare the latest Build number on the website (currently it is #630) with the version on my machine.

I think JKP's Name Manager will do everything you want in one tidy package. For example...
- It allows you to view hidden names in a dialogbox without changing the visible property in excel (a slight difference to Zorvek's "ShowNames"), and is thus useful for preventing unintended deletion/changes which may occur through the normal [alt + i + n + d] interface if all names were set to visible.
- you can filter for "names containing..."
- you can delete selected names via the addin's popup
- you can filter for names with external references
- etcetc

Of course, just as with Kevin's suggestion, it would be best to do a test run of any changes on a copy of your workbook.

Avatar of murphji


Great information, clear explanation. Thanks
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo