Copy sheet without creating new instances of named

2020-06-21 07:49发布

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:

enter image description here

1条回答
甜甜的少女心
2楼-- · 2020-06-21 08:32

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
查看更多
登录 后发表回答