Excel 2007 dynamic chart range correction


M

MikeF

*** 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
 
Ad

Advertisements

M

MikeF

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 said:
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/


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

Jon Peltier

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/


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 said:
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/


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

MikeF

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

Jon Peltier said:
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/


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 said:
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


.
.
 
D

Dave Peterson

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.


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

Jon Peltier said:
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/


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


.
.
 
Ad

Advertisements

M

MikeF

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

Dave Peterson said:
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.


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

Jon Peltier said:
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


.

.
 
D

Dave Peterson

Then qualify your range with the correct sheet:

Source:=worksheets("sheetnamethatholdsthatrange").Range("dynRngRevProg")

Is the sheet really named the same as the range?

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

Dave Peterson said:
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.


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


.

.
 
M

MikeF

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

Dave Peterson said:
Then qualify your range with the correct sheet:

Source:=worksheets("sheetnamethatholdsthatrange").Range("dynRngRevProg")

Is the sheet really named the same as the range?

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

Dave Peterson said:
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


.

.
 
D

Dave Peterson

activeworkbook.worksheets("whatisthenameoftheworksheet????").range("dynrngrevprog")

You'll have to spell workbooks correctly, too.

or
activeworkbook.names("dynrngrevprog").referstorange


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

Dave Peterson said:
Then qualify your range with the correct sheet:

Source:=worksheets("sheetnamethatholdsthatrange").Range("dynRngRevProg")

Is the sheet really named the same as the range?

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


.

.
 
M

MikeF

This one worked!!!

activeworkbook.names("dynRngRevProg").RefersToRange

Thanx Dave, as always.

- Mike


Dave Peterson said:
activeworkbook.worksheets("whatisthenameoftheworksheet????").range("dynrngrevprog")

You'll have to spell workbooks correctly, too.

or
activeworkbook.names("dynrngrevprog").referstorange


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

Dave Peterson said:
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


.

.
 
Ad

Advertisements

D

Dave Peterson

Qualifying the range with the sheet that owns that range should work, too.
This one worked!!!

activeworkbook.names("dynRngRevProg").RefersToRange

Thanx Dave, as always.

- Mike

Dave Peterson said:
activeworkbook.worksheets("whatisthenameoftheworksheet????").range("dynrngrevprog")

You'll have to spell workbooks correctly, too.

or
activeworkbook.names("dynrngrevprog").referstorange


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


.

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top