Delete Named Ranges That Referring to Another Workbook

[BigBeachBananas]

For context, I currently have a VBA that copies a sheet from sourceWB to destWB. My issue is that when you copy a sheet, the named ranges get copied over to the destWB as well. I'm looking for a VBA code that deletes any named ranges that refer to the sourceWB. I'm thinking of looking for any named ranges under RefersTo that start with a single quote and deleting it, however, my code doesn't look like it's doing it.

Sub DeleteNamedRangesReferringToOtherWorkbooks()
    Dim wb As Workbook
    Dim nm As Name
    Dim deleteList As New Collection
    Dim nameToDelete As String

    ' Loop through all open workbooks
    For Each wb In Workbooks
        ' Loop through all named ranges in the current workbook
        For Each nm In wb.Names
            nameToDelete = nm.Name
            ' Check if the named range refers to another workbook
            If Left(nameToDelete, 1) = "'" Then
                ' Add the named range to the delete list
                deleteList.Add nameToDelete
            End If
        Next nm
    Next wb

    ' Delete the named ranges in the delete list
    For Each nameToDelete In deleteList
        On Error Resume Next ' Ignore errors if the named range doesn't exist
        ThisWorkbook.Names(nameToDelete).Delete
        On Error GoTo 0 ' Reset error handling
    Next nameToDelete
End Sub

 

[kevin9999]

Assuming the following code is in the destination workbook, try this:

Sub Lose_Copied_Named_Ranges()
    Dim xName As Name
    For Each xName In Application.ThisWorkbook.Names
        If InStr(1, xName.RefersTo, "'") > 0 Then xName.Delete
    Next xName
End Sub

 

[NdNoviceHlp]

Hi BBB. Trial transferring your sheet using a collection. It will fix the named range issue. HTH. Dave

Dim ShtCollect As Collection
Set ShtCollect = New Collection
ShtCollect.Add sourceWB.Sheets("Sheet1")
ShtCollect(1).Copy before:=destWB.Sheets("Sheet1")

Adjust the sheet name(s) to suit.

 

[BigBeachBananas]

Thank you for the replies. Both solutions worked, but @kevin9999 's is more aligned with what I'm looking for.

 

[kevin9999]

Glad we could help & thanks for the feedback