Relative Reference NAME Ranges

[Gunswick]

How can I make creating a named range relative to the active cell? So that I can click on any cell and a named range is created in the adjacent column for 5 rows.
e.g. click cell c5, and a named range from cells d5:d10 is created when i run the macro

ActiveCell.Offset(0, 1).Range("A1:A11").Select
    ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!R5C3:R15C3"

this is what i have so far in my macro, I cant figure how to make it set the range specified by the offset, it always just uses absolute ranges from cell A1.

 

[Gunswick]

you are a legend, it works beautifully :D

This macro now makes a perfect copy of a template chart with unique source data that you specify by the active position when you run the macro.

this can obviously be edited for the ranges / series that i need in the future.

 

[RoryA]

Glad to help!