VBA - Custom defined names based on range in cell value

[pina1990]

Looking for a bit of help from a VBA noob here.

I'm trying to write a vba which will define a name of a table based on the range which is defined as text in a cell (this is to let me easily control the range of the table array based on its content).

The code I've been using is:

Range("B8:E40").Select
    ActiveWorkbook.Names.Add Name:="Beam_Summary1", RefersToR1C1:= _
        "='Beam Summary'!R8C2:R40C5"

I've got Cell P15 on worksheet 'Beam Summary' which defines the range of the table array and want to be able to introduce that instead of the range select and "='Beam Summary'!R8C2:R40C5" in the code above. I've tried defining a variable and using that but not really having any luck. Any ideas?

 

[StephenCrump]

Welcome to the Forum!

If 'Beam Summary'!P15 contains: B8:E40, then:

Range(Worksheets("Beam Summary").Range("P15").Value).Name = "Beam_Summary1"

will create BeamSummary1: ='Beam Summary'!$B$8:$E$40

 

[pina1990]

Amazing.

Thank you!