On Value Change Copy Data To Another Sheet

Hi All,

New to this site and VB ability is only fair. I have a sheet in Excel (sheet 1)which reads in eleven values. If the data in the 11th value changes then I would like to copy the other 10 values to sheet 2 appending to previous values copied. Date and time on each copy would also be nice if possible.

The values in sheet 1 are being read in via an OPC server from a machine and only change at the and of a batch which could take a few hours.

We are trying to gather this information as a test just for stocking levels on a short term basis. We may look at a more sophisticated method after our trial period is completed.

Any help or pointers would be appreciated.

Best Regards

Roth

Welcome to the Board.

I suspect you're going to need to elaborate a little re: the workflow and processes involved re: Sheet1 - some of us may not be as savvy re: the terminology in use

Originally Posted by Roth

The values in sheet 1 are being read in via an OPC server

So these cells are populated how exactly in laymans terms ? You have formulae or they are updated by an external query or they are populated via code... ?

The key is we need to understand what is triggering the change in the 11th value... and how the 11th value itself is physically altered (formulae, code etc...).
It is this which will in determine the appropriate event for capturing said change .... and it is the event that will in turn conduct/invoke your "snapshot".

Regards the trigger.. you say when it goes from 0 to 1 ... and thereafter ? ie presumably only if the value drops below 1 and back again would you wish to snapshot ?

Well unfortunately DDE updates won't function for me (obviously) but I what I would say is that given the >=1 trigger you could think of using a Static variable in your Worksheet_Calculate event - this will persist during the Excel "session" - this Static variable can (in effect) hold the value of the Trigger prior to current calculation and compare to current value as and when calculation is invoked... when the current Trigger value <> Static / Prior value AND the current trigger value >= 1 then (and only then) would you conduct the snapshot.

Please Login or Register  to view this content.

hopefully that gives pointers if nothing else...

(and to clarify DDE updates don't invoke the standard Change event... only the Calculate event (like other "standard" formulae))

Hi DonkeyOte,

Thanks for that, will try out and get back and advise on outcome, again thanks.

Hi All,

Just one little problem with the solution. The trigger value in cell B21 on sheet1 sometimes reads '#REF!' if there is a temporary loss of communication via RSLinx to the machine. This might happen once or twice a day, once a week or maybe once or twice an hour depending on the network. Anyway this brings up a Run-time error '13' : Type Mismatch which halts the code and prevents the batch info being copied until the 'End' button is clicked on the dialog box.
Is there anyway would could get the code to somehow ignore this little comms blip and continue executing the code. Otherwise we have to keep going to PC and checking that the code is running which defeats purpose of trying to collect this information automatically.
Again any help would be much appreciated.

Presuambly you would just want to exit in case of any error value (ie leave Static untouched) in which case simply validate the cell:

Please Login or Register  to view this content.

Hi DonkeyOte,

Thanks for the reply. Yes would be the answer. Essentially ignore the loss of data/communication when it occurs and allow code to execute when comms returns and trigger goes high. This way we don't have to keep checking that the code is running, i.e. making it a little bit more robust to comms losses. A little loss of data every now and then is ok as long as the code doesn't stop.

Again thanks for reply, will try it out and report back.