Problem with reloading graphs! (References to Nameranges)

[yonathan.sabbah]

Hi everyone,

Okay, lets see if somebody can help me with this. I have my little
graphics with some nameranges references:

ie:name ='Data-Daily'!F1
y values= 'Data-Daily'!Yvalues
x values= 'Data-Daily'!Xvalues

Now, the problem comes when I delete Data-Daily (because I want to
refresh the data or just any reason. My graph is too smart and the
references become #REF# because of course the sheet doesn't exist
anymore.

I have tried to use manual calculation and CALCULATE on the VBA code to
solve this. But even if the graph stays now the same, the references
are still changed.

Is there a way in VBA to cancel this automatic update of the ranges in
the graph so I can delete the sheet, put it back and then press F9 or
run CALCULATE to have them reloading correctly every time?

Regards,

Yona

 

[Jon Peltier]

Instead of deleting and putting back the sheet (presumably with new data)
can you paste the new data over the old? Use paste special - values or paste
special - formulas if a regular paste also messes up the links.

- Jon

 

[paul.bellrichard]

Yona,

Jon's suggestion is a good one... although I'm not sure how it will
hande your ranges "Yvalues" and "Xvalues".

You could use VBA to re-assign the graph series to dummy ranges (i.e.
"Yvalues_temp" and "Xvalues_temp") located on the same sheet as the
graph (or any sheet you don't plan to delete... even if it's hidden).
Then delete "Data-Daily". Then add the new "Data-Daily". Finally,
re-assign the graph series to the real ranges again.

Summary:
1 - Re-Assign Graph Series to dummy ranges
2 - Delete "Data-Daily"
3 - Add "Data-Daily"
4 - Re-Assign Graph Series to real ranges

I'm not sure how difficult it is to re-assign the data series in VBA.
Hope that helps.

--Paul