Dynamic Range for a Chart & Trendline

[Carrie_Loos via OfficeKB.com]

Can anyone help? I have a pivot table of values that I am using in a chart. I
am using a lookup formula to have a range of 100 possible values within the
worksheet. Then I run this little "update" macro. I have an dynamic range for
the 100 possible values. =OFFSET(Repair Learning Curve Coeff!$G$4,0,0,COUNTA
(Repair Learning Curve Coeff!$G$4:$G$103),1)

I keep getting an error on the :=ActiveSheet.Range("ChartData") line of
'Runtime error 1004' Application-defined or object-defined error.

The range is still showing from G4:G103 (I assume it's from the dynamic range
formula?)

If I put the range of G4:G103 in the ActiveSheet.Range I get past the error
but then I will get a message for the trendline of "Some trendlines cannot be
calculated from data containing negative or zero values". Again it's because
the range is not changing for only those cells with values. Not sure how to
rewrite things.

Any ideas?

Sub Update()
'
Dim NbrRepairs As Range
Dim ChartData As String


' copy values outside of pivot to allow forumula's to calculate in learning
curve graphs
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D9").Select
Application.CutCopyMode = False

' Uppdate graph
Set NbrRepairs = Range("K52")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

ActiveChart.SetSourceData Source:=ActiveSheet.Range("ChartData"), PlotBy:
=xlColumns

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScale = NbrRepairs
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

' Add Trendline
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPower, Forward:=0,
_
Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
ActiveWindow.Visible = False
Windows("Repair Manpower 2008 b.xls").Activate
Range("V42").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Range("C4").Select
End Sub