Update Chart Source Data
 

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...

Update Chart Source Data
 

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


"iamnu" wrote in message
...
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...

Update Chart Source Data
 

The better way to do this is to make your series using dynamic named ranges
insertnamedefinename 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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"iamnu" wrote in message
...
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...

Update Chart Source Data
 

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:=xlR 1C1)

Set Series2 = .Range("G2:G" & LastRow)
Series2Addr =
Series1.address(External:=true,ReferenceStyle:=xlR 1C1)
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165255

Microsoft Office Help

Update Chart Source Data
 

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

"Peter T" <peter_t@discussions wrote in message
...
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


"iamnu" wrote in message
...
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...