Update cell, automatically copy row to a separate

Question

I have a worksheet comprising of two columns (A and B) ... the first of which is just a name, the second is a number.

If I make an edit to a number in column B, I want Excel to automatically copy that entire row to a second worksheet in order to create a list of edits that I have made.

The second worksheet would then be a continually updated list of changes that I have made to the first sheet, with the latest change (a copy of the two updated columns) added to the next unused row.

I hope that a bit of VBA trickery might be able to make this happen, but require some help to make it happen.

Answer 1

Here is some additional code to check the row number, as per the above answer and comments.

Dim row_num As Long
row_num = Cells(Rows.Count, "B").End(xlUp).Row
If row_num > 1 then row_num = row_num + 1 'Add 1 only when row number doesn't equal to 1, otherwise - 1.

Answer 2

Try this in the sheet where you have data (under the Excel Objects), e.g Sheet1

Option Explicit
Dim PrevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then

    Application.ScreenUpdating = False

    Dim rng As Range
    Dim copyVal As String

    Set rng = Nothing
    Set rng = Range("A" & Target.Row & ":B" & Target.Row)

    'copy the values
    With Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)
        .Offset(1, 0).Resize(1, rng.Cells.Count).Value = rng.Value

        With Worksheets("Sheet1")
            Range("A" & Target.Row).Copy
            copyVal = CStr(PrevVal)
        End With

        .Offset(1, 0).PasteSpecial xlPasteFormats

        .Offset(1, 1) = copyVal
        Application.CutCopyMode = False

    End With
End If

Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub

PrevVal = Target.Value
End Sub