Delete nearly all defined names from the active worksheet only

Excel 2007

Need to delete nearly all defined names from the active worksheet only. Worksheets can be named anything.

These two worksheets must be excluded from having any names deleted:

.codename(“Sheet1”)
.name(“Summary”)

Also, I don’t want any ~system names deleted, so exclude:
(although Print_Area is the only one I’ve seen…)

*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria

I have been trying to get something like this working (from searching these and other forums):

Sub deleteinvalidnames()

Dim invalidname As name
Dim Ans As Integer
Dim Msg As String
 
For Each invalidname In ActiveWorkbook.Names
     If invalidname.RefersToRange.Worksheet.name = ActiveSheet And _
         Not invalidname.name Like "*Print_Area" And _
         ActiveSheet.CodeName <> "Sheet1" And _
         ActiveSheet.name <> "Summary" Then
              Msg = invalidname.name & " = " & invalidname.RefersToRange.Address
              Ans = MsgBox("Do you want to Delete/Rename: " & Msg, vbYesNo + vbInformation)
              If Ans = vbYes Then invalidname.Delete
     End If
  Next invalidname
 
‘Then run other macro to create new names (this has no issues)…
resetnames
 
End Sub

I’ve tried some mixes with:

Set ws = Activesheet
With ws
   For each invalidname in ws.names

etc. but nothing seems to work.

I don’t really need to cycle through with each name, but I came across that code first and it looked like a good thing to have in while troubleshooting…

http://www.mrexcel.com/forum/excel-q...heet-only.html

I would appreciate any advice as to how to proceed. Thank you.