Unable to Copy 1 cell Value (not the formula) to another sheet if condition in another cell is 1

[Ray1970]

Dear readers,

My head is falling off, I spent 30 hrs finding a solution for a simple issue. (i guess). I've read 30000 threads, copied code lines and tried everything I could think of.
(I'm absoluteley a VBA beginner). I work for an IT departememt with 130 people but no one can help me out. (?)

Let's start.
I've got a excel workbook withs connects to a plc. (that works with a macro from the manufacturer, that works fine).
The PLC puts 2 values in the sheet let's say in cell A1 and A2 with 2 simple formula's (fomula's are from the manufacturer> example in A1 =LOGOVAR("VB0.2") and in A2 =LOGOVAR("VB0.3")

Sheet1:
Value in Cell A1 is a zero or a 1
Value in cell A2 is a counter value from zero to max 100

What I want to do is when Cell A1 changes from a 0 to a 1 Copy the value in Cell A2 to the next sheet (say Sheet2) in columm D, in the next empty cell.

I've managed (thanks to all the threads in this forum) to get a copy and paste thing working but not when there is a formula in cell A1 and A2.
The script sees only the formula and not the output value.

So the first time I manually copy the (or antother formula) formula in cell A1 (when I know the output from the plc is 1 ; it works, then when the plc changes the value to a zero and back again to a 1 nothing happens.
Then when I 1 delete the formulas in Cell A1 and A2 and manually put a 1 in A1, and a value in A2, paf it works.

What I stole so far is


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Value = 0 Or IsEmpty(Target) Then Exit Sub
If Target.Cells.Value = 1 Then
If Not Intersect(Target, Range("$A$1")) Is Nothing Then


Worksheets("Sheet1").Range("A2").Copy
Dim Lastrow As Long
Lastrow = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1).Row

Sheets("Klanten").Cells(Lastrow, 4).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



End If

End If


End Sub




Is there anybody who can save 2022 for me?
Thanks in advance

 

[bebo021999]

Try with worksheets_calculate() even.

 

[Ray1970]

No, nothing happens at all, even if I change cell A1 manually to a 1

 

[Ray1970]

I restarted Excel again, the event works but it's the same story as i mentioned aboved, when there is a formula in A1, nothing happens.
Only manually changing the values will trigger the action. I've tried it with in A1 a link to another cell (=A4) same story, so the =LOGOVAR("VB0.2") formula itself doesnt seem the problem