when cell value changes, copy the information to a new worksheet but also retain each change

Hello. How would I copy a cell ( or row actually) to a new worksheet when I make a change to a particular cell in the first worksheet? AND whenever I make a change to that particular cell it would n...

Changing cell value

Macros & VBA

Logaraj Sekar
Apr 17, 2018
Try this.

But after entering the value press enter, don't try downarrow / tab .

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(-1, 0).Activate
Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub

dono12800

Copper Contributor

Jul 26, 2022

Logaraj Sekar sir, if I want to stop the recording of data to sheet 2 at a certain row e.g. row 5 and continue recording automatically to a next column and end it again at row 5 and start next column and so on, how do i alter the code to do that or do i need to add a separate code?

Thanks

wwoodin-2265

Copper Contributor

Oct 14, 2022

Here was the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
ActiveCell.Offset(-1, 0).Activate
a = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row + 1
Sheets("Sheet2").Range("C" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub

Forum Discussion

when cell value changes, copy the information to a new worksheet but also retain each change

Resources