How do I paste a source data reference to a chart using a macro?

I need to be able to draw a new graph as I change the parameters, but that
means that I would have to re-enter the source reference for the X and Y
values. In order to do that I concatenated a string that builds the address,
then I copy it and paste it to the chart's source references. I need to do
that with a macro. When I run the Excel macro utility to recreate all my
steps it inserts a reference that remains permanent. When I run the macro
again it does not paste the new reference but remains with the old one I
inserted previously.

I realized that I need to explain myself better. I first create a string
with the address as follows: =CONCATENATE("=Data!A$4:A$",TEXT(4+H3,"#")), the
I copy and paste special the value to another address.

Below is the macro subroutine to copy the resulting string:
Keyboard Shortcut: Ctrl+w
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Sheets("S-Curve").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Data!R4C1:R16C1"
ActiveWindow.Visible = False
Sheets("Data").Select
Range("K8").Select
Sheets("S-Curve").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Data!R4C3:R16C3"
ActiveWindow.Visible = False
Range("C2").Select
End Sub


"Pedro J. Davila" wrote:

> I need to be able to draw a new graph as I change the parameters, but that
> means that I would have to re-enter the source reference for the X and Y
> values. In order to do that I concatenated a string that builds the address,
> then I copy it and paste it to the chart's source references. I need to do
> that with a macro. When I run the Excel macro utility to recreate all my
> steps it inserts a reference that remains permanent. When I run the macro
> again it does not paste the new reference but remains with the old one I
> inserted previously.