VBA to Create Range Names from references

[Yoepy]

Hello, I hope someone can help.
I need some VBA to create 3 Range names based on column references. My problem is that there may be 100's so I would prefer not to do it manually.

I can create the references based on the columns, so column B would be as follows:

What is the code to look at the cell reference that contains the range name "EVENT1" and give it the reference $A$3:$B$23.

The Name "EVENT1" is in cell B26 and reference $A$3:$B$23 is in B30 (if that makes any difference).

Don't worry about a loop to do it multiple times.
[TABLE="width: 287"]
<tbody>[TR]
[TD]Range name 1
[/TD]
[TD]EVENT1
[/TD]
[/TR]
[TR]
[TD]Range name 2
[/TD]
[TD]EVENT1LessByes
[/TD]
[/TR]
[TR]
[TD]Range name 3
[/TD]
[TD]EVENT1Names
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Range1 Reference
[/TD]
[TD]$A$3:$B$23
[/TD]
[/TR]
[TR]
[TD]Range2 Reference
[/TD]
[TD]$A$4:$B$23
[/TD]
[/TR]
[TR]
[TD]Range3 Reference
[/TD]
[TD]$B$3:$B$23
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you

 

[offthelip]

It is not clear to me where your second and third names and ranges are so here is some code that will create a named range using the B26 and B30

Sub test()
   Dim ref2 As Range
   
    Nme = Range("B26")
    ref = Range("B30")
    wksnme = ActiveSheet.Name
    Set ref2 = Range(wksnme & "!" & ref)
    ActiveWorkbook.Names.Add Name:=Nme, RefersTo:=ref2


End Sub

 

[Yoepy]

Thank you offthelip