复制工作表而不创建命名范围的新实例? [英] Copy sheet without creating new instances of named ranges?

问题描述

我正在使用以下代码复制工作表.我也有一些名为范围的工作范围.问题是，当我进行复制时，它将创建具有新工作表范围的所有命名范围的重复项.一切正常，但我可能有20多个工作表.我不需要80个大多数重复的命名范围.如何避免这种情况?

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

复制后的名称管理器:

推荐答案

这是我的答案:

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

编辑#2

在阅读注释之后，这里是更新，该更新将工作表传递给仅循环"newSheet"

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

这篇关于复制工作表而不创建命名范围的新实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！