O nome de um intervalo pode ser dinâmico com várias planilhas no Microsoft Excel

Question

Eu sou um novato no VBA. O que eu gostaria de saber é que o nome de um intervalo pode ser dinâmico com várias planilhas. Eu pesquisei online e me deparei com uma pergunta semelhante feita por outra pessoa e fui respondido por alguém com o nome de usuário Gary's Student neste site Abaixo está uma cópia de seu código e link para a mensagem original.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
str = Range("A1").Text
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Dim n As Name
For Each n In ActiveWorkbook.Names
    If n.RefersTo = "=Sheet1!$A$2:$A$4" Then
        n.Delete
    End If
Next n
ActiveWorkbook.Names.Add Name:=str, RefersTo:="=Sheet1!$A$2:$A$4"
End Sub

O nome de um intervalo nomeado pode ser dinâmico?

Gostaria de saber se é possível duplicar Sheet1 junto com seu nome e intervalo dinâmicos e na mesma pasta de trabalho e chegar à Planilha2 (Código da Planilha) com nome diferente de intervalo (talvez com um intervalo diferente / igual de células). Eu tentei brincar com essa ideia, mas recebi uma mensagem de erro de

Compile error: 

Ambiguous name detected: Worksheet_Change

A razão pela qual eu preciso duplicar a planilha é porque Sheet1 será meu cálculo de custos para "Base-Case" e Sheet2 será para "Option 1". Opção 1 terá a maior parte do conteúdo como o "caso base", mas algumas alterações. Em seguida, criarei uma tabela de comparação para identificar as alterações entre as duas (ou mais, até um máximo de 4) planilhas.

Eu ficaria muito grato por qualquer ajuda.

Sean

Adição final à pergunta inicial

Depois de brincar com as planilhas em que estou trabalhando, agora percebo que preciso duplicar a planilha da planilha de dizer "Caso base", em que a planilha "Caso base" tem vários intervalos nomeados. O que eu preciso da nova planilha duplicada, digamos “Sheet2”, é ter os mesmos nomes dos intervalos nomeados de Sheet1, mas alguns desses intervalos se referirão a mais ou menos número de linhas. Por exemplo (veja abaixo), na Folha1 eu tenho 3 intervalos nomeados da seguinte forma.

--In Sheet1-- 
Name of named range 1: "Substructure"       Range:$A$20:$A40
Name of named range 2: "Superstructure"     Range:$A$42:$A60
Name of named range 3: "Finishes"           Range:$A$62:$A80

--In Sheet2-- 
Name of named range 1: "Substructure"       Range:$A$20:$A30
Name of named range 2: "Superstructure"     Range:$A$32:$A66
Name of named range 3: "Finishes"           Range:$A$68:$A100

Sean