Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with reloading graphs! (References to Nameranges)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with reloading graphs! (References to Nameranges)
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with reloading graphs! (References to Nameranges)
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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Reloading | Excel Programming | |||
saving and reloading the contents of a userform | Excel Programming | |||
Reloading values in optionbuttons or checkboxes | Excel Programming | |||
reloading a userform query | Excel Programming | |||
reloading arrays | Excel Programming |