Copy sheet without creating new instances of named ranges?

Question

I'm using the following code to copy a sheet. I also have a few named ranges that are scoped to the Workbook. The problem is, when I do the copy, it creates duplicates of all the named ranges with a scope of the new sheet. Everything works of course but I could potentially have 20+ sheets. I don't need 80 named ranges that are mostly duplicates. How can I avoid this?

Sub btnCopyTemplate()
    Dim template As Worksheet
    Dim newSheet As Worksheet
    Set template = ActiveWorkbook.Sheets("Template")
    template.Copy After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    newSheet.Name = "NewCopy"
End Sub

And the Name Manager after a copy:

Quick suggestion: Instead of copying the entire Sheet, what if instead you just copies all the Cells? That way, you would just keep the data, no named ranges. ie: Sheets("mainSheet").Cells.Copy Sheets("newSheet").Cells. — BruceWayne
You can delete the names after creating the sheet. I don't think there's a setting which controls whether they get created with the copy. — Tim Williams

Elbert Villarreal Elbert Villarreal · Accepted Answer · 2016-01-27T21:47:58

Here is my answer:

Sub btnCopyTemplate()
    Dim template As Worksheet
    Dim newSheet As Worksheet
    Set template = ActiveWorkbook.Sheets("Template")
    template.Copy After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    newSheet.Name = "NewCopy"
    deleteNames 'Check the sub
End Sub

Sub deleteNames()
    Dim theName As Name
    For Each theName In Names
        If TypeOf theName.Parent Is Worksheet Then
            theName.Delete
        End If
    Next
End Sub

This way you will delete all the names with the scope "worksheet" and keep the "workbook" names

Edit#2

After read the comments here is the update passing the sheet to loop only the "newSheet"

Sub btnCopyTemplate()
    Dim template As Worksheet
    Dim newSheet As Worksheet
    Set template = ActiveWorkbook.Sheets("Template")
    template.Copy After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    newSheet.Name = "NewCopy"
    deleteNames newSheet
End Sub

Sub deleteNames(sht As Worksheet)
    Dim theName As Name
    For Each theName In Names
        If (TypeOf theName.Parent Is Worksheet) And (sht.Name = theName.Parent.Name) Then
            theName.Delete
        End If
    Next
End Sub

Copy sheet without creating new instances of named ranges?

1 Answers