Update Chart Source Data

[iamnu]

I used the "Record Macro" function to get the code shown below. When
I recorded the macro, the value of A1 was 392. But when I actually
ran the macro, the value of A1 was 393. As you can see, the "Record
Macro" function "hard coded" the value of 392.

How can I make this work such that the SeriesCollection is updated
with the CURRENT value of A1?

Sheets("Chart2").Select
Range("A1").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(1).XValues = "='Approval Index'!
R2C1:R392C1"
ActiveChart.SeriesCollection(1).Values = "='Approval Index'!
R2C5:R392C5"
ActiveChart.SeriesCollection(2).XValues = "='Approval Index'!
R2C1:R392C1"
ActiveChart.SeriesCollection(2).Values = "='Approval Index'!
R2C7:R392C7"
Sheets("Data Input").Select
Range("A13").Select

Thanks for your help...

 

[Peter T]

You can make your Series update without code using a 'Dynamic Range Name'

Looks like your X/category values are in 'Approval Index'!A1:A#
Series1-Y values offset in col-E
Series2-Y values offset in Col-G
where # is the value in A1.

Define the following names,

myCat =OFFSET('Approval Index'!$A$1,1,0,'Approval Index'!$A$1-1)
mySeries1 =OFFSET(myCat,,4)
mySeries2 =OFFSET(myCat,,6)

If A1 is not on 'Approval Index' change the sheet name accordingly

Change your Series1 & 2 formulas to
=SERIES(,Book1.xls!myCat,Book1.xls!mySeries1,1)
=SERIES(,Book1.xls!myCat,Book1.xls!mySeries2,2)

Change the workbook name to suit

Regards,
Peter T

 

[Don Guillett]

The better way to do this is to make your series using dynamic named ranges
insert>name>define>name it something like graphrng1> in the refers to box>
=offset($a$1,1,0,counta($a:$a),1)
In the series, instead of hardcoded type in
=workbooknamehere!graphrng1
Do the same for the other series
Now your chart will be sell adjusting without using cell a1. Or, if you
REALLY need to use a1 then
=offset($a$1,1,0,$a$1,1)

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[joel]

I like crete data ranges the using the Address properties to get the
R1C1 addresses.


with Sheets("Approval Index")
..LastRow = .Range("A" & Rows.count).end(xlup).row
Sheets("Chart1").Select
Set ChartLabels = .Range("A2:A" & LastRow)
ChartLabelsAddr = ChartLabels.address( _
External:=true,ReferenceStyle:=xlR1C1)

Set Series1 = .Range("E2:E" & LastRow)
Series1Addr =
Series1.address(External:=true,ReferenceStyle:=xlR1C1)

Set Series2 = .Range("G2:G" & LastRow)
Series2Addr =
Series1.address(External:=true,ReferenceStyle:=xlR1C1)
end with

set MyChart = Sheets("Chart1")
Mychart.Select
ActiveChart.ChartArea.Select

with ActiveChart
..SeriesCollection(1).XValues = "=" & ChartLabelsAddr
..SeriesCollection(1).Values = "=' & Series1Addr
..SeriesCollection(2).XValues = "=" & ChartLabelsAddr
..SeriesCollection(2).Values = "=' & Series2Addr
end with

 

[Peter T]

Typo

Looks like your X/category values are in 'Approval Index'!A1:A#

should be
Looks like your X/category values are in 'Approval Index'!A2:A#

Peter T