Copy specific cells to new worksheet

[hopr37]

Trying to copy a specific cell(s) from sheet 1 to sheet 2 when a value in said cell changes.
example:
part 1.
sheet1 cell B2
value=1
sheet1 cell E2
Value is 10

Copies value from sheet1 cell B2 and sheet1 cell E2 to worksheet2 cell B2 and E2
part 2.
Sheet1 cell B2
Value changes to 2
sheet1 cell E2
Value is 20
copies new value from sheet1 cell B2 and E2 to sheet2 but moves new value down 1 cell to B3 and E3 but also leaving sheet2 cell B2 and cell E2 as is.
So everytime the value of sheet1 B2 and E2 changes, it copies that new changes to sheet2 but moves the new values down one row.

 

[NoSparks]

How do the value of said cells change? Formula, manually, macro ?

 

[hopr37]

Manual change

 

[NoSparks]

Use the Worksheet_Change event of sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
'limit to single cell at a time
If Target.Count > 1 Then Exit Sub
'limit to B2, E2 only
If Target.Address <> "$B$2" And Target.Address <> "$E$2" Then Exit Sub

Dim wr As Long      'write row

With Sheets("Sheet2")
    wr = .Cells(Rows.Count, "B").End(xlUp).Row + 1
    .Cells(wr, "B") = Sheets("Sheet1").Cells(2, "B")
    .Cells(wr, "E") = Sheets("Sheet1").Cells(2, "E")
End With

End Sub

 

[NoSparks]

.... uses wrong row if B2 happens to be blank, maybe get wr this way

    wr = .Cells.Columns("B:E").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1

 

[hopr37]

Thank you. I'll get back to you in a bit and see how it goes.

 

[hopr37]

Thanks. that worked perfect.
Thanks again!