Excel chart/graph

[Guest]

Hello..

There are 2 series in the bar chart one is for Sales and other for Goal and
X axis has the Year.

The following code refreshes only one series and deletes the other series
and X axis values.
Any change that I need to do in the code which refreshes both the the series
and X-axis.
*******************************
Sub SetChartDataSource()
Dim NewSet As String
Dim NewSet1 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address
NewSet = "B2:" & Range("B2").End(xlDown).Address
NewSet1 = "C2:" & Range("C2").End(xlDown).Address
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets(ActiveSheet.Name).Range(NewSet)
ActiveChart.SetSourceData Source:=Sheets(ActiveSheet.Name).Range(NewSet1)
Range(CurLocation).Select
End Sub
**********************************

 

[Andy Pope]

Hi,

The final setsourcedata was simply replacing the first.
Try this instead.

Sub SetChartDataSource()
Dim NewSet As String
Dim NewSet1 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address
NewSet = "B2:" & Range("B2").End(xlDown).Address
NewSet1 = "C2:" & Range("C2").End(xlDown).Address
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData _
Source:=Union(Sheets(ActiveSheet.Name).Range(NewSet), _
Sheets(ActiveSheet.Name).Range(NewSet1))
' ActiveChart.SetSourceData
'Source:=Sheets(ActiveSheet.Name).Range(NewSet1)
Range(CurLocation).Select
End Sub

Cheers
Andy