Trouble deleting defined names (Get run-time error 1004)

[bdautrich]

Working on an application to track inventory and orders, The application has several worksheets that are like databases with inventory or order info. We have gone live with the software but the users have found some issues and there are a few additional features we want to deploy. We have a new version of the software that fixes the bugs and adds the new features and I am working on a user form that the user can click on a button to copy these "datatbases" (Worksheets) to the new version of the application. I am able to copy the worksheets but when I copy the worksheets I also copy any defined names on them. To avoid duplicates I am trying to delete the the defined names in the new version of the application. I need them there to test the software to make sure it works. Here is the code to delete the defined names.

ActiveWorkbook.Names("Strains!Strains").Delete

When I run this I get Run time error number 1004 "an Application-defined or object-defined error .

To check the defined names I wrote this code to check on them

Set Sht = ThisWorkbook.Sheets("Debug") 'Make you change the sheet reference if needed

On Error Resume Next
For Each nm In Names
With Sht.Range("A" & Rows.Count).End(xlUp)(2)
.Value = nm.Name
Dname = nm.Name
MsgBox ("Defined Name is " & Dname)
.Offset(, 1) = "'" & nm.RefersTo
Dref = nm.RefersTo
MsgBox ("Defined name refers to " & Dref)
.Offset(, 2).Formula = "=Len(" & Sht.Range("A" & Rows.Count).End(xlUp).Address & ")"
End With
Next nm

And the output in spreadsheet and on screen tracks what I expect

Here is the output for the Strains database which is on a worksheet called Strains

Would like to fix this if possible. Only other option is to delete the defined names from the new version and tell user they must copy databases first before doing anything when we deploy the new version

Any help would be greatly apprecited

Strains

=Strains!$A$1:$A$153

7​

 

[Domenic]

It's probably because your defined name has workbook scope. If this is the case, try omitting the sheet reference like this...

ActiveWorkbook.Names("Strains").Delete

Hope this helps!

 

[bdautrich]

It's probably because your defined name has workbook scope. If this is the case, try omitting the sheet reference like this...

ActiveWorkbook.Names("Strains").Delete

Hope this helps!

Thanks for the attempt. I deleted the sheet reference to the first database sheet and that worked. I then did the same for the remaining 5 defined names to not reference the sheet the name is defined on.
I tried that and the first time I ran the code the error occurred on the second defined name. I removed the workbook reference and now none of the names are deleted. I restored the sheet reference without the workbook reference and that did not work. Any other suggestions.

Bruce

 

[bdautrich]

Forgot to mention I returned to your recommendation for the first database and now that is not working

 

[Domenic]

To delete all defined names from the active workbook, whether worksheet or workbook scope, try...

    Dim nm As Name
    
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next nm

To delete all defined names that have worksheet scope from your worksheet called "Debug", try...

    Dim nm As Name
    
    For Each nm In Worksheets("Debug").Names
        nm.Delete
    Next nm

 

[bdautrich]

To delete all defined names from the active workbook, whether worksheet or workbook scope, try...

    Dim nm As Name
   
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next nm

To delete all defined names that have worksheet scope from your worksheet called "Debug", try...

    Dim nm As Name
   
    For Each nm In Worksheets("Debug").Names
        nm.Delete
    Next nm

Thanks again for your help that got me closer

I have 4 sheets that are databases and 3 sheets that have various pick list info that can be configured without changing the software. One of the sheets has 4 different pick lists and hence 4 defined names.

So, when I added this code, I used the name of that sheet to see if it would delete only the defined names on that sheet. But it ended up deleting all of the defined names and it did not create an error.

I next ran the software again to see if it would do this again, thinking the problem was solved but the second time I ran the code the names were not deleted. So, when I delete the sheets before copying a sheet of the same name from the older version of the application. I end up with the defined names from the older version of the application. Which is what I want since the user could have edited the pick lists. But the second time I ran the software it did not delete the defined names not sure why. But also, not getting error. What this ends us is duplicate defined names one from the old app and the second name which comes from the new app with a refers to that started with #REF. The interesting thing is if I manually delete these names with the bad refernces and rerun the software it works. But runnin git again produces the same results that the names were not deleted. The only thing I can think of is the software somehow knows that when it copies the worksheets from the old app that it knows they did not come from the the new version application workbook, so the code above does not delete them. But this does not see to explain what I am seeing because it would not work the second time.

 

[Domenic]

To delete the defined names in a specific worksheet, for example "Debug", whether having workbook or worksheet scope, try the following...

    Dim nm As Name
   
    For Each nm In ActiveWorkbook.Names
        If InStr(1, nm, "Debug", vbTextCompare) > 0 Then
            nm.Delete
        End If
    Next nm

 

[bdautrich]

This works repeatably. Thanks!

To delete the defined names in a specific worksheet, for example "Debug", whether having workbook or worksheet scope, try the following...

    Dim nm As Name
  
    For Each nm In ActiveWorkbook.Names
        If InStr(1, nm, "Debug", vbTextCompare) > 0 Then
            nm.Delete
        End If
    Next nm

 

[Domenic]

That's great, glad I could help.

Cheers!