Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Prob with ChartSource DataData Range
I'm making a dashboard type report and have my first chart linked to:
DataRange=E!$C$14:$G$21 I copied the chart and want to do is change the E to a G to reference my other data sheet. Problem is that after I hit "ok" it changes =G!$C$14:$G$21 to ='G'!$C$14:$G$21 with the single apostrophes. Because of that, it thinks the entire range is a data label. This used to work but now it doesn't so I think I must have changed some kind of setting somewhere. Any help would be greatly appreciated. Thanks, Mike Zz |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Prob with ChartSource DataData Range
Even with the single quotes, Excel should recognize 'G' as a sheet name. You
don't happen to have a defined name 'G'? I just did this in a new workbook without any problem. What if you made a new workbook? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "MikeZz" wrote in message ... I'm making a dashboard type report and have my first chart linked to: DataRange=E!$C$14:$G$21 I copied the chart and want to do is change the E to a G to reference my other data sheet. Problem is that after I hit "ok" it changes =G!$C$14:$G$21 to ='G'!$C$14:$G$21 with the single apostrophes. Because of that, it thinks the entire range is a data label. This used to work but now it doesn't so I think I must have changed some kind of setting somewhere. Any help would be greatly appreciated. Thanks, Mike Zz |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Prob with ChartSource DataData Range
I checked for the name and there is none.
What I'm really trying to do is to have a macro automatically change the number of series and categories on a series of charts. Each chart refers to a different sheet. I thought that what I was doing before was the easiest way but found another potentially easier way. Unfortunately, I can't get this to work either... This code dies on the ActiveChart.Delete line. When I recorded the macro, it created that line when I went into chartSourceData and deleted the DataRange. When I did that, it seemed to keep the chart structure in place, just removed the data. Basic background info: Charts are all on sheet "Report". Charts are named "ChartA", "ChartB", etc... Data for ChartA is on sheet "A", Data for ChartB is on sheet "B" Each data sheet (A,B, etc) have a range called "ChartData" Sub UpdateChartDims2() Dim oChart As ChartObject Dim cht As Object, sh As Worksheet For Each oChart In ActiveSheet.ChartObjects chtName = oChart.Name chtSheet = Replace(chtName, "Chart", "") ActiveSheet.ChartObjects(chtName).Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets(chtSheet).Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects(chtName).Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True Next End Sub "Jon Peltier" wrote: Even with the single quotes, Excel should recognize 'G' as a sheet name. You don't happen to have a defined name 'G'? I just did this in a new workbook without any problem. What if you made a new workbook? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "MikeZz" wrote in message ... I'm making a dashboard type report and have my first chart linked to: DataRange=E!$C$14:$G$21 I copied the chart and want to do is change the E to a G to reference my other data sheet. Problem is that after I hit "ok" it changes =G!$C$14:$G$21 to ='G'!$C$14:$G$21 with the single apostrophes. Because of that, it thinks the entire range is a data label. This used to work but now it doesn't so I think I must have changed some kind of setting somewhere. Any help would be greatly appreciated. Thanks, Mike Zz |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Prob with ChartSource DataData Range
Mike -
You're doing a lot of extra activating and such. Try a more streamlined approach: Sub UpdateChartDims2() Dim oChart As ChartObject Dim cht As Chart, sh As Worksheet Dim chtName As String, chtSheet As String For Each oChart In Sheets("Report").ChartObjects chtName = oChart.Name chtSheet = Replace(chtName, "Chart", "") Set cht = Sheets("Report").ChartObjects(chtName).Chart cht.SetSourceData Source:=Sheets(chtSheet).Range("ChartData") Next End Sub Not completely tested, but it looks about right. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "MikeZz" wrote in message ... I checked for the name and there is none. What I'm really trying to do is to have a macro automatically change the number of series and categories on a series of charts. Each chart refers to a different sheet. I thought that what I was doing before was the easiest way but found another potentially easier way. Unfortunately, I can't get this to work either... This code dies on the ActiveChart.Delete line. When I recorded the macro, it created that line when I went into chartSourceData and deleted the DataRange. When I did that, it seemed to keep the chart structure in place, just removed the data. Basic background info: Charts are all on sheet "Report". Charts are named "ChartA", "ChartB", etc... Data for ChartA is on sheet "A", Data for ChartB is on sheet "B" Each data sheet (A,B, etc) have a range called "ChartData" Sub UpdateChartDims2() Dim oChart As ChartObject Dim cht As Object, sh As Worksheet For Each oChart In ActiveSheet.ChartObjects chtName = oChart.Name chtSheet = Replace(chtName, "Chart", "") ActiveSheet.ChartObjects(chtName).Activate ActiveChart.ChartArea.Select Application.CutCopyMode = False ActiveChart.Delete ActiveWindow.Visible = False Windows("Measurables Chart 8-Panel v7.xls").Activate Sheets(chtSheet).Select Application.Goto Reference:="ChartData" Selection.Copy Sheets("Report").Select ActiveSheet.ChartObjects(chtName).Activate ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _ CategoryLabels:=True, Replace:=True, NewSeries:=True Next End Sub "Jon Peltier" wrote: Even with the single quotes, Excel should recognize 'G' as a sheet name. You don't happen to have a defined name 'G'? I just did this in a new workbook without any problem. What if you made a new workbook? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "MikeZz" wrote in message ... I'm making a dashboard type report and have my first chart linked to: DataRange=E!$C$14:$G$21 I copied the chart and want to do is change the E to a G to reference my other data sheet. Problem is that after I hit "ok" it changes =G!$C$14:$G$21 to ='G'!$C$14:$G$21 with the single apostrophes. Because of that, it thinks the entire range is a data label. This used to work but now it doesn't so I think I must have changed some kind of setting somewhere. Any help would be greatly appreciated. Thanks, Mike Zz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Wrap Text Across Columns & Rows | Excel Discussion (Misc queries) | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions |