Copy of some Named Ranges to new Workbook not working

[HKafeman]

I hope someone can help with this VERY strange issue?

We use the following code to copy 3 worksheets to a new spreadsheet:
<Worksheet1Name>.Copy
With ActiveWorkbook
<Worksheet2Name>.Copy .Worksheets(1)
<Worksheet3Name>.Copy .Worksheets(2)
:: :: ::

This has been working okay for a very long time. But strangely now some Named Ranges which have (only) Workbook scope, from Worksheet2 do not get copied across and then are not available to other Spreadsheets accessing the new Spreadsheet when it haas been saved.

When the Worksheet2 is selected in the new Spreadsheet, the missing Names are not shown in the Formula Bar dropdown, but are visible in Name Manager, but with the full path back to the original Spreadsheet, not just "<Worksheet2Name>!$B$42" in the "Refers To"! - "Edit Links" fails to remove the linkage when "Break Links" is tried as well!

If I delete and re-create the Named Range I still get the same problem. If I create a new Named Range with a different name, but for the same single Cell then the new names work okay!

So, can anyone shed any light on what could be happening and why or how to investigate further? Plus importantly how to fix this???

It is holding up our development of a vital Spreadsheet, which needs to be able to access the Named Range that is in existing saved Spreadsheets and to save to new Spreadsheets when it created them!

This is driving me crazy! - Why should some Named Ranges work as before and some now not be working!?!

Thanks

 

[HKafeman]

Please can someone help with this?
I have found some similar posts, but with no solutions, so this is definitely an issue with Excel, not just with our Spreadsheet, etc.!!!
Hopefully one of you has an understanding of how/why this happens and how to resolve or work around???

 

[HKafeman]

Meanwhile I have managed to sort out a workaround!

It relies on the "ValidWorkbookParameter" Property of the Named Range being False if there is a problem with it! - I spotted this to be the case for my "TroublesomeName" versus other Named Ranges that are working okay!

Does anyone know what the "ValidWorkbookParameter" Property is supposed to mean and in what circumstances it is set to True versus False? - I really would like to know why it is False now in my case when previously it was working okay!

    Dim lNmCt As Long
    Dim bTroublesomeNameOk As Boolean
    Dim sTroublesomeNameRefersTo As String
    Dim sTroublesomeNameCell As String
    ::    ::    ::
    With ActiveWorkbook
        For lNmCt = .Names.Count To 1 Step -1
            If (.Names(lNmCt).Name = "TroublesomeName") Then
                If (.Names(lNmCt).ValidWorkbookParameter) Then
                    bTroublesomeNameOk = True
                Else
                    sTroublesomeNameRefersTo = .Names("TroublesomeName").RefersTo
                    'Get the actual cell reference after "!"
                    sTroublesomeNameCell = Right(sTroublesomeNameRefersTo, Len(sTroublesomeNameRefersTo) - InStr(sTroublesomeNameRefersTo, "!"))
                End If
                Exit For
            End If
        Next
    ::    ::    ::
        If (Not bTroublesomeNameOk) Then
            ' Add TroublesomeName back in so refers to new Spreadsheet and not back to the one that created it!
            .Names.Add Name:="TroublesomeName", RefersTo:=.Worksheets("sys_LinkedFilesData").Range(sTroublesomeNameCell)
        End If
    End With