Have changed the sheet name to RevProg.
The [dynamic] range's scope is Workbook.
Tried ...
Source:=ActiveWorkbood.Range("dynRngRevProg")
... But that didn't work either.
This *should* be able to work --- shouldn't it??
Thanx.
- Mike
:
Then qualify your range with the correct sheet:
Source:=worksheets("sheetnamethatholdsthatrange").Range("dynRngRevProg")
Is the sheet really named the same as the range?
MikeF wrote:
Neither of those work, they both are yellowed completely in Debug.
However, think I know what the problem is, would be my bad for not
mentioning initially.
The Sheets("dynRngRevProg").Select is correct, but the chart is *copied*
onto that sheet from ALT-F1. When the sheet is selected, the chart is not.
The chart's name is "Chart 2", without the quotations.
What would the syntax be to select the sheet, *then* select the chart - to
make it active?
Thanx,
- Mike
:
Since that line is in the with/end with structure:
With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
The .range(...) refers to the activechart.
So either try:
With ActiveChart
.SetSourceData _
Source:=.parent.parent.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
or
With ActiveChart
.SetSourceData _
Source:=activesheet.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
The .parent of the chart is the chartobject (Jon will correct me (soon!)). The
..parent of the .parent (or the parent of the chartobject) is the worksheet that
owns the chart.
MikeF wrote:
Jon,
The sub stalls, and "blues out" specifically the following:
.Range
Error is....
Compile Error
"Method or Data Member Not Found"
Definitely both the sheet and range names are absolutely correct.
Is there anything I'm missing, syntax, or ..??
Thanx.
- Mike
:
This was written for a chart embedded on the active worksheet.
For a chart on a standalone sheet, this should do it:
Sub UpdateChartSourceData()
Sheets("dynRngRevProg").Select
With ActiveChart
.SetSourceData _
Source:=.Range("dynRngRevProg"), _
PlotBy:=xlColumns
End With
End Sub
- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/
On 4/14/2010 5:43 PM, MikeF wrote:
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
:
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/dynamic-chart-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(SalesProgress!dynRngSalesProg)
… But the chart [series] turns that into a static range.
On 4/3/2010 the chart’s 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
.
.
