Prob with Chart>Source Data>Data Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 chart>SourceData 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
 
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
 
Back
Top