How to Remove Weird Names while copying Tabs

#imwasi #wasi_learning #remove_weird_names_excel #excel

How to Remove Weird Names while copying Tabs

How to Remove Weird Names while copying Tabs
How to Remove Weird Names while copying Tabs

Excel some times irritates us by giving errors we don’t know about, so consequently we have to work more to sort these issues or we have to press OK to these errors when we don’t know about the solution, A similar situation can be faced by most of you when excel gives following error:

A formula or sheet you want to move or copy contains the name ‘XXXX’, which already exists on the destination worksheet. Do you want to use this version of the name?
To use the name as defined in 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.

error can be seen in below image. Due to this error some time we have to press enter for some seconds or some times even for minutes to copy a sheet. as this thing is irritating so we will discuss its solution today.

How to Remove Weird Names while copying Tabs
How to Remove Weird Names while copying Tabs

If you click on no following will appear

How to Remove Weird Names while copying Tabs
How to Remove Weird Names while copying Tabs

So our today’s question is :

How to Remove Weird Names while copying Tabs

or how to tackle warning of

A formula or sheet you want to move or copy contains the name ‘XXXX’, which already exists on the destination worksheet

So there is a simple method, We can use a macro to remove these weird names, Kindly keep in mind that most of the time “Name Manger” is not able to show these names so basically these are hidden names in a sheet.
Following procedure will be used to remove these irritating names so that we can copy sheet with a click of mouse.

  1. Press “Alt and F11” at the same time to open developers options. This option must be enabled in your excel, click here to know how can we enable it.
  2. A new screen will appear just like below mentioned image.

    Remove Hidden Cell Image 1
    Remove Hidden Cell Image 1
  3. Now delete ever thing written here and paste following code there:

    Sub TOOLS_DELETENAMEDRANGE()

    Dim nm As name
    On Error Resume Next

    For Each nm In ActiveWorkbook.Names
    If (MsgBox("Delete name- " & nm.name, vbYesNo) = vbYes) Then
    nm.Delete
    End If
    Next

    On Error GoTo 0

    End Sub

  4. Now press “fn and f5” at same time to run this macro.
  5. You will see a prompt that these weird names are deleted now.
  6. Now you can copy your tabs easily and with just a click.

If this trick saves your time than do share our post and sit with your friends. Thank You

Post Author: wasi

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.