0

I am a newbie on VBA. What I would like to know is can the name of a range be dynamic with multiple sheets. I have searched online and came across a similar question asked by someone else and was answered by someone with the user name of Gary’s Student from this website Below is a copy of his/her code and link to the original message.

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

Can the name of a named range be dynamic?

I'd like to know if it is possible to duplicate Sheet1 along with its dynamic name and range and in the same workbook and arriving at say Sheet2 (Worksheet Code) with different name of range (maybe with a different/same range of cells). I have tried playing around with this idea but receiving an error message of 

Compile error: 

Ambiguous name detected: Worksheet_Change

The reason I need to duplicate the worksheet is because Sheet1 will be my calculation of costs for "Base-Case" and Sheet2 will be for "Option 1". Option 1 will have most of the contents as the "Base-Case" but some changes. I will then create a comparison table to identify the changes between the two (or more, up to a maximum of 4) worksheets.

I would be very grateful for any helps.

Sean

Late Addition to initial question

After playing around with the worksheets I am working on, I now realise that I need to be able to duplicate worksheet from the say “Base Case” worksheet where the “Base Case” worksheet has a number of named ranges. What I will need the duplicated new worksheet, say “Sheet2” to do is to have the same names of the named ranges of Sheet1 but some of these ranges will refer to more or less number of rows. For example (see below), in Sheet1 I have 3 named ranges as follows.

--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

Sean L
  • 9
  • 2
  • if Data Range are common then it's possible to create Dynamic Range across workbook. Like this can be used to Name Range `='Sheet1:Shee4'!$A$2:$C$20` – Rajesh Sinha Aug 29 '18 at 07:46
  • Hi Rajesh,Thanks for the very quick response. I have added more question(s) to my original question and would be grateful if could take a look. – Sean L Aug 29 '18 at 11:14
  • If you want to have the same name refer to different ranges on different worksheets, you will need them to have worksheet scope and not workbook scope. – Ron Rosenfeld Aug 30 '18 at 10:04
  • @SeanL, no need to create duplicate Named Range since, Named Range is/are accessible across workbook ! And always remember Excel never allows to create duplicate NAME to even different Range ! – Rajesh Sinha Sep 03 '18 at 08:35

0 Answers0