VBA Macro Repeats and Will Not Stop

Question

Hi All,

My VBA code will not stop. My spreadsheet is being fed by an API. The code is design to trigger on the value of a cell. Before the value to check the code asks if another value is 1, this cell value is inputted by the code and if it equals 1 it stops the code from executing.

The code checks 3 different values, The value of 1 never appears so the code continues to cut and paste. I am sure I have something mixed up or wrong but I am not getting any compile errors or debug so the code looks correct just maybe not in the correct order. I stop the code with application events if the value is 1, problem is the value never becomes 1. I have change around the TRU and False order but it does not work either way. Code below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheet1
If ws.Range("BM1").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK1").Value = 10 Then
With Range("BC10:BC68").Copy
Range("BC10").PasteSpecial Paste:=xlPasteValues
Range("BM1") = 1
End With
Range("BM1").Value = 1
Application.EnableEvents = False
End If
If ws.Range("BM2").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK2").Value = 5 Then
With Range("BD10:BD68").Copy
Range("BD10").PasteSpecial Paste:=xlPasteValues
Range("BM2") = 1
End With
Range("BM2").Value = 1
Application.EnableEvents = False
End If
If ws.Range("BM3").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK3").Value = 105 Then
With Range("BE10:BE68").Copy
Range("BE10").PasteSpecial Paste:=xlPasteValues
Range("BM3") = 1
End With
Range("BM3").Value = 1
Application.EnableEvents = False
End If
End Sub

Many Thanks

Jonathan

Answer 1

Sorry, just a correction, it is not a macro it is VBA code, thanks

Answer 2

The main problem is that you have to set Application.EnableEvents to False before modifying cells, then to True afterwards, instead of the other way round.
Apart from that, it's not entirely clear what you want to react to. Does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("BM1"), Target) Is Nothing Then
        If Range("BM1").Value <> 1 And Range("BK1").Value = 10 Then
            Application.EnableEvents = False
            Range("BC10:BC68").Copy
            Range("BC10").PasteSpecial Paste:=xlPasteValues
            Range("BM1").Value = 1
        End If
    End If
    If Not Intersect(Range("BM2"), Target) Is Nothing Then
        If Range("BM2").Value <> 1 And Range("BK2").Value = 5 Then
            Application.EnableEvents = False
            Range("BD10:BD68").Copy
            Range("BD10").PasteSpecial Paste:=xlPasteValues
            Range("BM2").Value = 1
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("BM3"), Target) Is Nothing Then
        If Range("BM3").Value <> 1 And Range("BK").Value = 105 Then
            Application.EnableEvents = False
            Range("BE10:BE68").Copy
            Range("BE10").PasteSpecial Paste:=xlPasteValues
            Range("BM3").Value = 1
            Application.EnableEvents = True
        End If
    End If
End Sub

Answer 3

Hans,

Just to add, I have multiple sheets so need to be able to repeat to procure for each of my multiple sheets

Thanks

Jonathan

Answer 4

Should the code be identical for all sheets?
Do all sheets need to look at BKn and BMn on their own sheet or on Sheet1?

Answer 5

Each sheet is identical, 40 different sheets for sheet 1 to 40, each sheet has a separate set of data. All sheets look at BKn and BMn