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.

Private Sub Worksheet_Calculate()
Static boolPrior As Boolean, boolCurrent As Boolean
boolCurrent = Range("B21") >= 1
If boolCurrent = boolPrior Then Exit Sub Else boolPrior = boolCurrent
On Error GoTo ExitPoint
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
If boolCurrent Then
    Range("B1,B3,B5,B7,B9,B11,B13,B15,B17,B19").Copy
    With Sheets("Sheet1")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 10).PasteSpecial xlPasteValues, Transpose:=True
        .Cells(.Rows.Count, "A").End(xlUp).End(xlToRight).Offset(, 1).Value = Now
    End With
    Application.CutCopyMode = False
End If
ExitPoint:
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

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))