Local names remain when sheet is deleted

[bk1]

I have a really weird problem. I have a lot of local names on many of my sheets in my workbook. The sheets are deleted by VBA macros.

Now, for some of my sheets the local names are converted to global names and the sheet name becomes REF#. Is there some kind of logic behind when this happens, because I cannot seem to find it. Which names are staying behind seem to depend on something I cannot deduce.

 

[Derek Brown]

Hello and welcome to The Board.
I think that the problem is as a result of the 'scope' of the "Name" (e.g. workbook or worksheet).
It is easier to see the scope in the Excel 2007 Name Manager.
If it is 'worksheet' scope then it will be removed from Name Manager list when the worksheet is deleted. For 'workbook' scope it appears to remain but because the sheet to which it refers has been deleted, then it gives #REF for the worksheet name.
You could run code to remove invalid names but always make a backup copy of a workbook before making a big change (just in case!).
Something like the following - add a prompt to confirm deletion if you want to.

Option Explicit
Sub Delete_Invalid_Named_Formulas()
Dim oName As Object
For Each oName In ActiveWorkbook.Names
    If InStr(1, oName.RefersTo, "#REF") > 0 Then
        oName.Delete
    End If
Next
Set oName = Nothing
End Sub

 

[bk1]

Well, yes this is what I thought was going on at first, but that was not the case. It is indeed local names with a specific worksheet scope that is being converted - sometimes.