Refreshing Chart

[Joe]

Hello Experts ..

1. I have a worksheet, Col 1 Year, Col 2 Target, Col3 Actual data of a
products (separate sheet for each product)
2. Data from external DB is populated in Excel.
3. Excel is used to generate the trend chart and bar charts etc.
4. Since the source data (number of rows change in excel) the graph also
changes every time(Number of rows used for chart might increase or decrease)
for every product.
5. Based on the data I want to refresh the chart using the Macro.

Example:

Year Sales Goal
1980 5 7
1981 9 11
1982 13 15
1983 17 19
1984 21 23
1985 25 27
1986 29 31
1987 33 35
1988 37 39
1989 41 43
1990 45 47
1991 49 51
1992 53 55
1993 57 59
1994 61 63
1995 65 67
1996 69 71
1997 73 75
1998 77 79
1999 81 83
2000 85 87

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