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:
____thinkcellM0YAAAAAAAAAAA9 (it goes on with random-looking stuff
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?

murphjiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
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.
0
zorvek (Kevin Jones)ConsultantCommented:
You might have some hidden and/or corrupt names.

Names are introduced into a workbook many different ways. The most common is when a user creates a name for the purposes of making formulas and VBA code easier to write and maintain. Excel also generates a few names for printing areas, tables, and other tasks.

Names can also be introduced when copying cells or worksheets from another workbook. Some of these names will end up as invalid references. If deleting a sheet with names the names are not deleted from the workbook but will contain #REF! errors. All of these names are harmless for the most part but can be a sign of workbook corruption, especially if they cannot be deleted.

The steps below involve placing macros in the Excel VBA project and running them.

To install and run a macro, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

The first step to cleaning up names is to unhide all the hidden names. The macro below does that.

Public Sub ShowNames()
   Dim Name As Name
   For Each Name In ThisWorkbook.Names
      Name.Visible = True
   Next Name
End Sub

If only dealing with a few names then, from Excel, choose the menu command Insert->Name->Define and delete any unwanted names. If any name cannot be deleted and there are no more than 10 or 20 names, choose the menu command Tools->Options, navigate to the General tab, and change the “R1C1 reference style checkbox”. This forces Excel to examine all names and, if any are bad, it will ask for a new name. This process repeats for every bad name and so it can get tedious which is why this step is really only useful if there are a maximum of 10 to 20 bad names. Once the names have been renamed they can usually then be deleted using the Define Names dialog.

If there are more than a few names then continue with the following. Before proceeding make sure the workbook is backed up in the event that errors start appearing in the workbook.

The macro below deletes any names with bad references.

Public Sub DeleteBadNames()
   Dim Name As Name
   On Error Resume Next
   For Each Name In ThisWorkbook.Names
      If InStr(Name.RefersTo, "#REF") > 0 Then Name.Delete
   Next Name
End Sub

Additionally, if no references to external workbooks are desired, run the macro below to delete all names containing external references.

Public Sub DeleteExternalReferences()
   Dim NameDeleted As Boolean
   Dim Name As Name
   On Error Resume Next
   Do
      NameDeleted = False
      For Each Name In ThisWorkbook.Names
         If Name.Name = "Print_Area" Then Stop
         If Mid(Name.RefersTo, 2, 1) = "[" Or Mid(Name.RefersTo, 2, 2) = "'[" Or InStr(LCase(Name.RefersTo), ".xls]") > 0 Then
            Name.Delete
            If Err.Number = 0 Then NameDeleted = True
         End If
      Next Name
   Loop Until Not NameDeleted
End Sub

Finally, some macros and add-ins will create many names to support their functions. If those names can be identified somehow by looking at the name or the reference then use the macro template below to delete them. Change the conditional statements to suit the specific needs.

Public Sub DeleteSpecificNames()
   Dim Name As Name
   On Error Resume Next
   For Each Name In ThisWorkbook.Names
      ' Delete any names that begin with "BB"
      If Left(Name.Name, 2) = "BB" Then Name.Delete
      ' Delete any names with references begining with ={
      If Left(Name.RefersTo, 2) = "={" Then Name.Delete
      ' Delete any names with references begining with ="
      If Left(Name.RefersTo, 2) = "=""" Then Name.Delete
   Next Name
End Sub

If the above steps do not resolve all of the name issues or if there are names that cannot be deleted, then proceed with the following steps. Follow these instructions very carefully. If any step does not work as described then stop.

1) Close all copies of Excel.

2) Make a copy of the master workbook.

3) Open the copied workbook.

4) Press CTRL+F11, select the menu command Insert->Module to create a new VBA module, and paste the code below into the document window that appears. Press ALT+F11 to return to the Excel workbook.

[Begin Code Segment]

Public Sub RenameBadNames()

   Dim ReferenceStyleSave As Long
   Dim OriginallySelectedWorksheet As Worksheet
   Dim DummyWorksheet As Worksheet
   Dim Count As Long
   Dim CellValue As String
   Dim NameValue As String
   
   ReferenceStyleSave = Application.ReferenceStyle
   Application.ReferenceStyle = xlA1

   Set OriginallySelectedWorksheet = ActiveSheet
   Worksheets.Add After:=Worksheets(Worksheets.Count)
   Set DummyWorksheet = Worksheets(Worksheets.Count)
   DummyWorksheet.[A1].Select
   
   Count = 1
   Do
      DummyWorksheet.[A1].ClearContents
      DoEvents
      SendKeys "%C{ENTER}ZZZZZZDELETEME" & Count & "{ENTER}{ESCAPE}"
      Application.Dialogs(xlDialogOptionsGeneral).Show
      DoEvents
      If DummyWorksheet.[A1].Value = "ZZZZZZDELETEME" & Count Then Exit Do
      Count = Count + 1
      Application.StatusBar = "Renaming name " & Count
   Loop
   Application.ReferenceStyle = xlA1
   Application.StatusBar = False
   
   OriginallySelectedWorksheet.Activate
   
   Application.DisplayAlerts = False
   DummyWorksheet.Delete
   Application.DisplayAlerts = True
   
   If Count > 1 Then
      MsgBox Count - 1 & "names renamed."
   Else
      MsgBox "No names renamed."
   End If

End Sub

Public Sub DeleteBadNames()

   Dim Name As Name
   
   For Each Name In ThisWorkbook.Names
      If Left(Name.Name, 14) = "ZZZZZZDELETEME" Then
         Name.Delete
      ElseIf InStr(Name.RefersTo, "#REF") > 0 Then
         Name.Delete
      ElseIf Name.RefersTo = "=" & ThisWorkbook.Name & "!" & Name.Name Or Name.RefersTo = "='" & ThisWorkbook.Name & "'!" & Name.Name Then
         Name.Delete
      End If
   Next Name

   MsgBox "Done."

End Sub

[End Code Segment]

5) Press ALT+F8, select the macro "RenameBadNames", and click the Run command button to start the macro. The macro will run for a number of seconds. The progress will be displayed in the status area at the bottom of the Excel window. A dialog will be displayed when the macro is done. The macro renames bad names by leveraging an Excel feature that forces the renaming of the first bad name found when switching the referencing mode from A1 to R1C1 from the Excel UI.

6) Save the workbook with a new file name.

7) Close Excel (it may crash at this point - that's OK - the previous macro tends to stress Excel but does not seem to damage the workbook.)

8) Open the copy of the workbook saved in step 6.

9) Press ALT+F8, select the macro "DeleteBadNames", and click the Run command button to start the macro.

10) Save the workbook again.

At this point all bad names should be deleted. In some very rare cases even this last technique will not work. In that case the only way to purge the bad name from the workbook is to open the workbook file in a hex editor, find the name in the file data, and change any illegal characters to legal characters. Obviously this last technique is risky and should only be done on copies of the original workbook.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
broro183Commented:
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
0
murphjiAuthor Commented:
Great information, clear explanation. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.