VBA change event code

[jamescooper]

I have the following code which tracks changes to cells in the appropriate columns.

Private Sub Worksheet_Calculate()


nR = Cells(Rows.Count, "AG").End(xlUp).Row + 1
nR2 = Cells(Rows.Count, "AK").End(xlUp).Row + 1
nR3 = Cells(Rows.Count, "AO").End(xlUp).Row + 1
nR4 = Cells(Rows.Count, "AS").End(xlUp).Row + 1
nR5 = Cells(Rows.Count, "AW").End(xlUp).Row + 1
nR6 = Cells(Rows.Count, "BA").End(xlUp).Row + 1


inarr0 = Range("H1")
inarr = Range("K9:K10")
inarr2 = Range("K11:K12")
inarr3 = Range("K13:K14")
inarr4 = Range("K15:K16")
inarr5 = Range("K17:K18")
inarr6 = Range("K19:K20")


If inarr(1, 1) <> oldk9 Or inarr(2, 1) <> oldk10 Then


Application.EnableEvents = False


Range("AH" & nR) = inarr(1, 1)
Range("AI" & nR) = inarr(2, 1)
oldk9 = inarr(1, 1)
oldk10 = inarr(2, 1)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Sheet1").Range("AG" & nR)


Application.EnableEvents = True


End If

End Sub

I now want to add the following code which copies to another sheet when cell H1 says "Suspended". This works but then it continues to do this over and over and I only want it to do it once? Any ideas? Many thanks.

 If Range("H1") = "Suspended" Then
        Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)

 

[offthelip]

Use the same technique that you are using ( which I showed you) to save the old value of H1 and compare it with the current value of H1 , only do the copy if H1 is "Suspended" and oldH1 is not "Suspended"

 

[jamescooper]

Ok thanks so the challenge this time is that I am copying to another sheet called "Data", This is what I have so far.. Do I have to put a worksheet_calculate sub in there?

inarr0 = Range("H1")

If inarr0(1, 1) = "Suspended" Then


Application.EnableEvents = False
Range ("AG1:BC1000")


Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)
   
    End If

Thanks.

 

[offthelip]

Your code is not correct try this:

If inarr0(1, 1) <> "Suspended" And Range("H1") = "Suspended" Then

Application.EnableEvents = False
Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)
End If
inarr0 = Range("H1")
Application.EnableEvents = TRUE

You do NOT need to put anything in the worksheet_calculate sub of the DATA sheet.

Note the order you do things in is important : moving the statement inarr0=Range("H1") to then end aloows us to compare this times value of H1 with last times value

 

[jamescooper]

Your code is not correct try this:

If inarr0(1, 1) <> "Suspended" And Range("H1") = "Suspended" Then

Application.EnableEvents = False
Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)
End If
inarr0 = Range("H1")
Application.EnableEvents = TRUE

You do NOT need to put anything in the worksheet_calculate sub of the DATA sheet.

Note the order you do things in is important : moving the statement inarr0=Range("H1") to then end aloows us to compare this times value of H1 with last times value

Thanks for your help so far...Putting this at the end?

inarr0 = Range("H1")

At the end means when it tries to run it says it isn't defined

 

[jamescooper]

Any ideas?

thanks.

 

[offthelip]

the clue is in the error mesage you get "it says it isn't defined"
if you look back at my post #4 in your original thread :https://www.mrexcel.com/forum/excel-questions/1072971-vba-change-event-code.html
you will see that I told you to define the variables in a standard module. you need to do this for your inarr0 varaible as well.

 

[jamescooper]

the clue is in the error mesage you get "it says it isn't defined"
if you look back at my post #4 in your original thread :https://www.mrexcel.com/forum/excel-questions/1072971-vba-change-event-code.html
you will see that I told you to define the variables in a standard module. you need to do this for your inarr0 varaible as well.

Thanks, this is what i now have in Module 1

Public oldh1
Public oldk9
Public oldk10
Public oldk11
Public oldk12
Public oldk13
Public oldk14
Public oldk15
Public oldk16
Public oldk17
Public oldk18
Public oldk19
Public oldk20


Sub test()


End Sub

However, still receiving the same message?

Many thanks.

 

[jamescooper]

If inarr0(1, 1) <> [COLOR=#ff0000]oldh1[/COLOR] And Range("H1") = "Suspended" Then


Application.EnableEvents = False


Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)


End If


inarr0 = Range("H1")


Application.EnableEvents = True


End Sub

Should that bit in red be changed?

Many thanks.

 

[offthelip]

No you need to add:
public inarr0
to module 1

You also need to change the line with th error back to what I had it as

If inarr0(1, 1) <> "Suspended" And Range("H1") = "Suspended" Then