Copy worksheet - lots of names already exist

Posted on 2011-10-03
1 Ratings
Last Modified: 2012-05-12
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?

Question by:murphji
    LVL 17

    Expert Comment

    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.
    LVL 81

    Accepted Solution

    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
          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
                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)
       Count = 1
          SendKeys "%C{ENTER}ZZZZZZDELETEME" & Count & "{ENTER}{ESCAPE}"
          If DummyWorksheet.[A1].Value = "ZZZZZZDELETEME" & Count Then Exit Do
          Count = Count + 1
          Application.StatusBar = "Renaming name " & Count
       Application.ReferenceStyle = xlA1
       Application.StatusBar = False
       Application.DisplayAlerts = False
       Application.DisplayAlerts = True
       If Count > 1 Then
          MsgBox Count - 1 & "names renamed."
          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
          ElseIf InStr(Name.RefersTo, "#REF") > 0 Then
          ElseIf Name.RefersTo = "=" & ThisWorkbook.Name & "!" & Name.Name Or Name.RefersTo = "='" & ThisWorkbook.Name & "'!" & Name.Name Then
          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.

    LVL 10

    Expert Comment

    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, ( and Matthew Henson ( and can be downloaded from:

    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.


    Author Closing Comment

    Great information, clear explanation. Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now