non-Changing Chart

Hello,

Is it possible to create a xy scatter chart based on certain series and then when you change the data in the sheet the chart stays put.

This is why: I have written a code where calculations are made and the results appear in certain columns. After that it creates a chart. Now, when it repeats that for the next data-set, I need the old chart to stay the the same so I can compare it with the new chart which will be created. Is this possible?

This is how the code creates the chart as a new sheet:

Option Explicit

Sub AddChartObject()
'This sub creates a chart with the correlation tkane over 2 years
Dim TimeRange As Range
Dim Prod1WProd2W As Range
Dim Prod1WProd2Oil As Range
Dim Prod1OilProd2W As Range
Dim Prod1OilProd2Oil As Range
Dim Prod1InjProd2W As Range
Dim Prod1InjProd2OIL As Range

AddNewChart 'Adds a chart with variable name
RemoveUnwantedSeries 'Removes bad series
Sheets(2).Select

Set TimeRange = Sheets("Correlation").Range("W5:W795")
Set Prod1WProd2W = Sheets("Correlation").Range("K5:K795")
Set Prod1WProd2Oil = Sheets("Correlation").Range("M5:M795")
Set Prod1OilProd2W = Sheets("Correlation").Range("O5:O795")
Set Prod1OilProd2Oil = Sheets("Correlation").Range("Q5:Q795")
Set Prod1InjProd2W = Sheets("Correlation").Range("S5:S795")
Set Prod1InjProd2OIL = Sheets("Correlation").Range("U5:U795")

With ActiveChart.SeriesCollection.NewSeries
.Name = Sheets("Correlation").Range("X1:X2")
.Values = Prod1WProd2Oil
.XValues = TimeRange
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = "Prod1WProd2Oil"
.Values = Prod1WProd2W
.XValues = TimeRange
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = "Prod1OilProd2W"
.Values = Prod1OilProd2W
.XValues = TimeRange
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = "Prod1OilProd2Oil"
.Values = Prod1WProd2Oil
.XValues = TimeRange
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = "Prod1InjProd2W"
.Values = Prod1InjProd2W
.XValues = TimeRange
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = "Prod1InjProd2OIL"
.Values = Prod1InjProd2W
.XValues = TimeRange
End With
End Sub

Hello Schaep,

Yes it is possible.

Most straightforward way is to copy the chart and paste as a picture (look at CopyPicture method).

Another way is to convert the range references into arrays as mentioned by John Walkenbach here:
http://j-walk.com/ss/excel/usertips/tip009.htm

Regards,
Colin

Oh yeah, thank you. The array changing method did not work because of too long formula, but the copy paste method is fine enough.