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.
Examples:
____thinkcellM0YAAAAAAAAAA
Several other thinkcell names
___123Graph_B
Several others containing 123Graph
_MatInverse_In
_Sort
_Table1_In1
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?
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.
Examples:
____thinkcellM0YAAAAAAAAAA
Several other thinkcell names
___123Graph_B
Several others containing 123Graph
_MatInverse_In
_Sort
_Table1_In1
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?
Last Comment
andrewssd3
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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.
hth
Rob
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.
hth
Rob
ASKER
Great information, clear explanation. Thanks
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.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
