Paste value once

[Godwin117]

I have this VBA, however, when I run it, it repeats until I get a runtime error. I am trying to get it to where it pastes the cell's value if it reaches over a text limit. The limit would be 20. This cell(A2) has a card scanned and pastes the values of the card in the cell, instead of clicking a button every time, thought it would be quicker to have it automatically refresh if the criteria is met. There is a helper cell in G1 that verifies if the cell meets the requirements or not. Any help would be greatly appreciated.

Sub worksheet_change(ByVal target As Range)
Set target = Range("G1")
If target.Value = "YES" Then
Range("A2:C3").Select
Selection.Copy
Worksheets("Mass Assign").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Range("A2").ClearContent

End If

End Sub

 

[DanteAmor]

There is a helper cell in G1 that verifies if the cell meets the requirements or not.

In cell G1 do you have a formula?
Is it in cell A2 where you put the scanned data?

If the above is correct, try this:

Sub worksheet_change(ByVal Target As Range)
  If Target.Address(0, 0) = "A2" Then
    If Range("G1").Value = "YES" Then
      Application.ScreenUpdating = False
      Range("A2:C3").Copy
      Worksheets("Mass Assign").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
      Application.EnableEvents = False
      Range("A2").ClearContents
      Range("A2").Select
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End If
  End If
End Sub