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
