Copy values as they're entered into a column & paste to another worksheet - where am I going wrong?

Question

vAs the title suggests, I'm trying to copy and paste values to keep as a log as these values will be deleted shortly after they've been entered.

Long story short, a user will scan a barcode and a unique string of characters that correspond to the scanned barcode will be automatically entered into whichever cell is selected (usually A2). These strings are parent barcodes, so immediately after one has been scanned into A2 the user will start scanning their related children barcodes into B2, B3 and so on depending on how many children there are (always a completely random number).

Here's what I have so far:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim intRow As Integer
    Dim intCount As Integer

    Set ws = Worksheets("log")
    intRow = ws.Range("A1").CurrentRegion.Rows.Count + 1

    'Check if WandID exist in log.
    If Target.Address = "$A$2" Then
        intCount = Application.WorksheetFunction.CountIf(ws.Range("D:D"), Target.Value)
        If intCount <> 0 Then
            Target.Interior.Color = RGB(255, 0, 0)
            MsgBox "this wandID has already been used, please try a different barcode"
            Range("A2").ClearContents
            End
        Else
            Target.Interior.Color = xlNone
            ws.Cells(intRow, "D") = Target.Parent.Range("A2")
            ws.Cells(intRow, "E") = Target
        End If
    End If


End Sub

On the destination worksheet (named 'log') I've set up a simple table to organise the copied / scanned data and added some dummy data as a first entry - seeing as this workbook will be used regularly I wanted this process to keep adding scanned codes beneath the previous (dummy parent code in D2, dummy child codes in E2:E9), so ideally when I enter a new parent code into A2 of the source sheet, it should be copied to D10 on the destination sheet, and any corresponding child codes added to B2 etc. of the source sheet should be copied to E10 etc. of the destination sheet. Instead what's happening is the parent code will be added to D10:E10.

I have a feeling the line

"ws.Cells(intRow, "E") = Target"

is the problem, but not sure of what to change it to. Any help or advice would be hugely appreciated!

Side notes: use `Long` instead of `Integer`, and see [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the "right" way to find the last row. — BigBen, Dec 05 '19 at 14:41
Also - `Range("A2").ClearContents` causes the `Worksheet_Change` event to fire again. — BigBen, Dec 05 '19 at 14:42
Those two lines transferring cells appear to me to be equivalent. Also make sure you disable events before changing cells to avoid entering an endless loop. — SJR, Dec 05 '19 at 14:42
@BigBen thanks for the advice I've changed integer to Long and commented the clearcontents line. — lynX, Dec 05 '19 at 14:58
I think you need an extra if to see if `worksheet_change` has been triggered by an edit in column A of source meaning you need to write to column D of log or if its been triggered by a change to column B meaning you need to write to column E. At the moment you just check to see if change was in A2, and then write it to column D and E in log. Also, what SJR means is when you `clear_contents` you will trigger `worksheet_changed` again creating a loop — maxhob17, Dec 05 '19 at 15:15
What I meant was that `Target.Parent.Range("A2")` is the same as `Target` as the latter is A2. — SJR, Dec 05 '19 at 16:02

score 0 · Answer 1 · answered Dec 05 '19 at 15:52

I believe the code below will help you achieve your objective, it checks for the Next Free Row on Column D and that's where the Parent ID will be entered, similarly it will check the Child IDs in Column B and find the Next available row on Column E to paste the list into, the main changes are the use of Application.EnableEvents and the copy of data from Column B to Column E:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Worksheets("log")
Dim NextRow As Long, ChildRow As Long, ChildNextRow As Long

NextRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Offset(1, 0).Row
'get the Next Free row in Column D
ChildRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'get the Last Row with data on Column B
ChildNextRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Offset(1, 0).Row
'get the Next Free Row on Column E

    'Check if WandID exist in log (Column D).
If Target.Address = "$A$2" Then
    Application.EnableEvents = False
    'Turn Events off, so it doesn't fire the Change Event while we write to it
    intCount = Application.WorksheetFunction.CountIf(ws.Range("D:D"), Target.Value)
    'check if Parent ID has been used previously by check Column D
    If intCount <> 0 Then
        Target.Interior.Color = RGB(255, 0, 0)
        MsgBox "This wandID has already been used, please try a different barcode", vbInformation, "Duplicate Entry"
        ws.Range("A2").ClearContents
    Else
        Target.Interior.Color = xlNone
        ws.Cells(NextRow, "D") = ws.Range("A2")
        ws.Range("B2:B" & ChildRow).Copy Destination:=ws.Range("E" & ChildNextRow)
    End If
    Application.EnableEvents = True
End If
End Sub

Copy values as they're entered into a column & paste to another worksheet - where am I going wrong?

1 Answers1