VBA: copy sheet mantain defined name

Question

I have an excel sheet with defined names I would like to copy it with VBA by copying also the defined names. How can I do?

My current macro to copy the sheet:

Sub myMacro()
Const BASE_NAME As String = "MySheet"
Dim sheet_name As String
Dim i As Integer
Dim num_text As String
Dim new_num As Integer
Dim max_num As Integer
Dim new_sheet As Worksheet

' Find the largest number in a sheet name after the
' base name.
max_num = 0
For i = 1 To Sheets.Count
    sheet_name = Sheets(i).Name
    If Left$(sheet_name, Len(BASE_NAME)) = BASE_NAME _
        Then
        num_text = Mid$(sheet_name, Len(BASE_NAME) + 1)
        new_num = Val(num_text)
        If new_num > max_num Then max_num = new_num
    End If
Next i

' Make a new sheet with a new number.
Set new_sheet = Sheets.Add(after:=Sheets(Sheets.Count))
new_sheet.Name = BASE_NAME & Format$(max_num + 1)
new_sheet.Select
Sheets("MySheet_template").Range("A1:DQ1109").Copy 

Destination:=Sheets(new_sheet.Name).Range("A1")
End Sub

Take a look at this answer and this answer to see if that helps. — PeterT
Actually I could not solve. I cannot manage to copy the sheet and keep its defined names. — Thegamer23

curious curious · Accepted Answer · 2017-06-24T04:21:04

Try this - a slightly different approach

Sub myMacro()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim sh As Shape, strtSh As Worksheet
    Set strtSh = ActiveSheet
    Sheets("MySheet_template").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "MySheet" & Sheets.Count - 1
    For Each sh In ActiveSheet.Shapes
        sh.Delete
    Next sh
    strtSh.Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

VBA: copy sheet mantain defined name

1 Answers