|
Excel 2007 dynamic chart range correction
*** 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 |
Excel 2007 dynamic chart range correction
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 |
Excel 2007 dynamic chart range correction
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 . |
Excel 2007 dynamic chart range correction
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 "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 . |
Excel 2007 dynamic chart range correction
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" wrote: 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 "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 . . |
Excel 2007 dynamic chart range correction
Since that line is in the with/end with structu
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 "Jon Peltier" wrote: 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 "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 . . -- Dave Peterson |
Excel 2007 dynamic chart range correction
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" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . |
Excel 2007 dynamic chart range correction
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 "Dave Peterson" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . -- Dave Peterson |
Excel 2007 dynamic chart range correction
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" wrote: 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 "Dave Peterson" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . -- Dave Peterson . |
Excel 2007 dynamic chart range correction
activeworkbook.worksheets("whatisthenameoftheworks heet????").range("dynrngrevprog")
You'll have to spell workbooks correctly, too. or activeworkbook.names("dynrngrevprog").referstorang e MikeF wrote: 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" wrote: 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 "Dave Peterson" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Excel 2007 dynamic chart range correction
This one worked!!! activeworkbook.names("dynRngRevProg").RefersToRang e Thanx Dave, as always. - Mike "Dave Peterson" wrote: activeworkbook.worksheets("whatisthenameoftheworks heet????").range("dynrngrevprog") You'll have to spell workbooks correctly, too. or activeworkbook.names("dynrngrevprog").referstorang e MikeF wrote: 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" wrote: 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 "Dave Peterson" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
Excel 2007 dynamic chart range correction
Qualifying the range with the sheet that owns that range should work, too.
MikeF wrote: This one worked!!! activeworkbook.names("dynRngRevProg").RefersToRang e Thanx Dave, as always. - Mike "Dave Peterson" wrote: activeworkbook.worksheets("whatisthenameoftheworks heet????").range("dynrngrevprog") You'll have to spell workbooks correctly, too. or activeworkbook.names("dynrngrevprog").referstorang e MikeF wrote: 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" wrote: 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 "Dave Peterson" wrote: Since that line is in the with/end with structu 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 "Jon Peltier" wrote: 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 "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 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 . . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com