"Saxman" wrote in message
...
Jon Peltier wrote:
Let's say you have two sheets, the "Data" sheet which keeps refreshing
and the "Archive" sheet where you will store old data.

You can set up an event procedure on the Data worksheet. Right click the
Data worksheet tab and choose View Code. In the module that pops up,
choose Worksheet from the left dropdown, and Calculate from the right.
This code appears:

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Delete the second pair of lines:

Private Sub Worksheet_Calculate()

End Sub

Almost there, but I'm really confused about the above statement.

The following code comes first?

Private Sub Worksheet_Calculate()

End Sub

What code do I delete?


This is irrelevant, but the VB Editor writes it when you select Worksheet:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)



End Sub




Can I delete the original graph on the 'Archive' worksheet once it has
been copied to the 'Data' worksheet?


Yes, only keep the charts you need.



No values have been copied across in my example even though I have checked
the data and naming. I'll have to re-check or start again if you can
answer my query and see if that helps.


It's hard to generalize these techniques for any particular data structure.
If you're not experienced in dynamic charts, and using names as chart source
data, it might take a few tries to get all the details right. It's a
complicated abstraction.



Do you have an example on your website?


I don't have an example of this dynamic data updating on my web site, though
there are a number of dynamic chart examples using defined names. I only got
into these DDE-type problems recently for a potential client, and don't have
too much I can share.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______