Hello,
I am working on a program which pulls data into an excel sheet at
fixed intervals - the interval is determined by another computer and
cannot be controlled by excel. All the data show up on sheet1. I
have 8 data coming in per interval, and one of them is an interval
counter. I would like to use the counter to trigger a macro which
will store the data on sheet1 onto sheet2. So, every time the counter
increments, I want to run the macro. I am using the code shown
below.

Here is what I have figured out so far. If I set up a button to
manually change the value in the interval cell, then the macro works
as it should. But if I let the program simply read in the values from
the PLC (where the data is coming from), the macro never activates.
Please help.

thanks,
Andrew

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Cells(3, 16)) Is Nothing Then ' cells(3,16)
represents the interval count'
Exit Sub

Else
Dim y As Integer
y = Worksheets("monitor").Cells(3, 16) ' minute counter for
experiment'

Dim x As Integer
x = Worksheets("monitor").Cells(12, 5) 'this represents the on/off
status of the PLC'

If x = 1 Then
Worksheets("data").Cells(2, 2) = Date ' insert date'
Worksheets("data").Cells(3, 2) = Time() ' start time'
Worksheets("data").Cells(2, 4) = Worksheets("monitor").Cells(4, 5)
' set point'
Worksheets("data").Cells(4, 4) = Worksheets("monitor").Cells(5, 5)
' ramp time'
Worksheets("data").Cells(3, 4) = Worksheets("monitor").Cells(19, 5)
' slope'
Worksheets("data").Cells(5, 2) = Worksheets("monitor").Cells(6, 5)
' duration set'


Worksheets("data").Cells(y + 7, 1) =
Worksheets("monitor").Cells(20, 5) ' minute counter'
Worksheets("data").Cells(y + 7, 2) =
Worksheets("monitor").Cells(14, 5) ' flume flow'
Worksheets("data").Cells(y + 7, 3) =
Worksheets("monitor").Cells(15, 5) ' head tank flow'
Worksheets("data").Cells(y + 7, 4) =
Worksheets("monitor").Cells(16, 5) ' bypass % open'
Worksheets("data").Cells(y + 7, 5) =
Worksheets("monitor").Cells(17, 5) ' head tank % open'
Worksheets("data").Cells(y + 7, 6) =
Worksheets("monitor").Cells(18, 5) ' pump 1 rpm'
Worksheets("data").Cells(y + 7, 7) =
Worksheets("monitor").Cells(19, 5) ' pump 2 rpm'
End If

' Auto save every 60 samples'
'---------------------------'
Dim s As Integer
s = y Mod 60
If s = 0 And y 1 Then
ThisWorkbook.Save
End If


End If
End Sub

The Worksheet_Change event does not fire as a result of a calculation, only
as a result of a 'manual' change in a constant. Can you use the
Worksheet_Calculate event?:

When the calculate event runs, have the event code assign a counter to an
incrementing variable (and/or you could increment the value of a cell).

Next time the Calculate event runs, check the NEW counter value against the
OLD counter value. If different, run the copy.


--
---
HTH
Roger
Shaftesbury (UK)


"Andrew" wrote in message
oups.com...
Hello,
I am working on a program which pulls data into an excel sheet at
fixed intervals - the interval is determined by another computer and
cannot be controlled by excel. All the data show up on sheet1. I
have 8 data coming in per interval, and one of them is an interval
counter. I would like to use the counter to trigger a macro which
will store the data on sheet1 onto sheet2. So, every time the counter
increments, I want to run the macro. I am using the code shown
below.

Here is what I have figured out so far. If I set up a button to
manually change the value in the interval cell, then the macro works
as it should. But if I let the program simply read in the values from
the PLC (where the data is coming from), the macro never activates.
Please help.

thanks,
Andrew

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Cells(3, 16)) Is Nothing Then ' cells(3,16)
represents the interval count'
Exit Sub

Else
Dim y As Integer
y = Worksheets("monitor").Cells(3, 16) ' minute counter for
experiment'

Dim x As Integer
x = Worksheets("monitor").Cells(12, 5) 'this represents the on/off
status of the PLC'

If x = 1 Then
Worksheets("data").Cells(2, 2) = Date ' insert date'
Worksheets("data").Cells(3, 2) = Time() ' start time'
Worksheets("data").Cells(2, 4) = Worksheets("monitor").Cells(4, 5)
' set point'
Worksheets("data").Cells(4, 4) = Worksheets("monitor").Cells(5, 5)
' ramp time'
Worksheets("data").Cells(3, 4) = Worksheets("monitor").Cells(19, 5)
' slope'
Worksheets("data").Cells(5, 2) = Worksheets("monitor").Cells(6, 5)
' duration set'


Worksheets("data").Cells(y + 7, 1) =
Worksheets("monitor").Cells(20, 5) ' minute counter'
Worksheets("data").Cells(y + 7, 2) =
Worksheets("monitor").Cells(14, 5) ' flume flow'
Worksheets("data").Cells(y + 7, 3) =
Worksheets("monitor").Cells(15, 5) ' head tank flow'
Worksheets("data").Cells(y + 7, 4) =
Worksheets("monitor").Cells(16, 5) ' bypass % open'
Worksheets("data").Cells(y + 7, 5) =
Worksheets("monitor").Cells(17, 5) ' head tank % open'
Worksheets("data").Cells(y + 7, 6) =
Worksheets("monitor").Cells(18, 5) ' pump 1 rpm'
Worksheets("data").Cells(y + 7, 7) =
Worksheets("monitor").Cells(19, 5) ' pump 2 rpm'
End If

' Auto save every 60 samples'
'---------------------------'
Dim s As Integer
s = y Mod 60
If s = 0 And y 1 Then
ThisWorkbook.Save
End If


End If
End Sub