Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy sheet without creating new instances of named ranges?

Tags:

excel

vba

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

like image 704
THE JOATMON Avatar asked Jan 27 '16 21:01

THE JOATMON


People also ask

Can you copy named ranges in Excel?

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.

How do you copy an Excel sheet without the data?

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.

Can I use named ranges throughout the worksheet?

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.


1 Answers

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
like image 118
Elbert Villarreal Avatar answered Sep 19 '22 02:09

Elbert Villarreal