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.

 

[RoryA]

    ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!RC[1]:R[5]C[1]"

should work.

 

[Gunswick]

that works perfectly, thank you very much!

what about making the name unique? so that I could create multiple named ranges.

i dont mind what the names actually are, as long as they are unique. perhaps a random number generator or an incremental counter?

(this is to help make copying charts with different data sources easier by the way).

 

[RoryA]

Ah, I think I may have misunderstood. That creates a named range that is always one column to the right of the activecell and 6 rows high. So if you select D5, it refers to E5:E10. If you want a fixed reference that is established relative to the cell that is active at the time, then you need something like this:

    ActiveWorkbook.Names.Add Name:="fygainloss" & ActiveCell.Address(0, 0), _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(6).Address

 

[Gunswick]

its basicly so that i can copy a template chart for about 30 different records, which are all on one sheet.

so that i can just click on the top left cell of each "record", i.e the information that will go into the chart (e.g. 2007 gains + losses, 2008 gains+losses etc)

so i need to combine what you wrote for me...

ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!RC[1]:R[10]C[1]"

and..

Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"

which works, it copies the chart and updates the data (there are actually 8 series, but its the same thing repeated 8 times).

so i can now just click on the top left of each record and it makes a chart (rather than manually editing the data source of each copied chart).

However I need to make 30 individual ones, so obviously if i set new ranges for the same names, i will have 30 charts with the same info! = uselss

so i need to make the name unique, so that i can create each chart uniquely.

I dont think randomise will do it well, so i think i need an counter for the name. e.g. fygainloss1, fygainloss2, fygainloss3 etc for each named range i create with your code

 

[RoryA]

The version I just posted appends the cell address to the name of the range so they will all be unique.

 

[Gunswick]

ah yes i see, i didnt understand it before. It uses offset instead of the rc[0]r[1]c[0] etc

so that gives a unique name, which i need. So then how do i get it into my series change code

Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"

so that fygainloss is fygainlossActiveCellAddress (i.e. the unique name we just created) ?

 

[RoryA]

Dunno! How do you know which range relates to which chart?

 

[Gunswick]

as an example, i name the range and as you intended it becomes fygainlossB47 for example

so i need to get fygainlossB47 into my data series code in the chart, else the charts are not unique. can we use the active cell code you used before? fygainloss" & ActiveCell.Address(0, 0)

e.g.

Sub chartcopy8()
 
'set my range as a unique name (thanks rorya)
      ActiveWorkbook.Names.Add Name:="fygainloss" & ActiveCell.Address(0, 0), _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(11).Address
 
 
'selects my template chart, and makes a copy
     Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
'changes the series range from the original to my new unique name range 
'(should be fygainlossB47 for example)
      ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"      
 
End Sub

 

[RoryA]

Try this:

   Dim strName As String
   strName = "fygainloss" & ActiveCell.Address(0, 0)
'set my range as a unique name (thanks rorya)
      ActiveWorkbook.Names.Add Name:=strName, _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(11).Address
 
 
'selects my template chart, and makes a copy
     Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
'changes the series range from the original to my new unique name range
'(should be fygainlossB47 for example)
      ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!" & strName