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:
To do this, go to the Formulas tab and click on Name Manager. Then, select the named range you want to copy and click on the Copy button. You will then be able to paste the named range into another worksheet. Another way to copy a named range is to use the Create From Selection feature.
Simply hold down the Ctrl key, then click and drag the sheet's tab. When you release the mouse, Excel will create an exact copy of the sheet.
You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With