Jon,
Thanx for the assistance.
As follows is the adjusted sub:
Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub
.... It goes to the correct page, but Debug yellows the following:
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
Am I missing proper syntax re chart name or something else on the first line?
- Mike
"Jon Peltier" wrote:
Excel does not let you use named ranges for source data for the whole
chart, only for the components (name, x values, y values) for each series.
You may be able to use this approach to get around this limitation:
Dynamic Chart Source Data (VBA)
http://peltiertech.com/WordPress/dyn...t-source-data/
- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/
On 4/13/2010 11:46 AM, MikeF wrote:
*** There was a mistake in my original post, please disregard and refer only
to the following. Thank you. ***
The table below feeds a 3d Column Chart in Excel 2007.
It needs to be dynamic, so when figures for 4/6/2010, 4/7/2010, etc are
added the chart updates appropriately.
The €ś4/1/2010€ť is in cell c1.
There is a dynamic range in use, where c1.cc6 is named €śdynRngSalesProg€ť €¦
=OFFSET(SalesProgress!$C$1,0,0,6,COUNTA(SalesProgr ess!dynRngSalesProg)
€¦ But the chart [series] turns that into a static range.
On 4/3/2010 the charts Select Data showed the following:
='SalesProgress'!$A$1:$A$6,'SalesProgress'!$C$1:$E $6
Even though what was driving the chart originally was:
='SalesProgress'!$A$1:$A$6,OFFSET(SalesProgress!$C $1,0,0,5,COUNTA(SalesProgress!dynRngSalesProg).
I had to manually adjust the chart in Select Data for the series from $e$6
to $f$6.
Is there anything that can be done with this situation?
REGION 4/1/2010 4/2/2010 4/3/2010 4/5/2010
North 2,266 2,499
West 1,219 1,344
South 3,158 3,468
East 2,815 2,874 3,001 3,040
Canada 2,702 2,745 2,780 2,882
.