Forum Discussion

Brass Contributor

Apr 13, 2018

Solved

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

Logaraj Sekar

Steel Contributor

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

wwoodin-2265

Copper Contributor

Oct 14, 2022

Logara Seker
When it saves to sheet 2 it saves all in column B, Can you make so sheet 2 go across like B2,C2,D2 etc. instead of B2,B3,B4 etc.? Tried changing code but no luck

Umakant22030
Copper Contributor
May 26, 2023
This is what exactly I'm also looking for.
When I update D2 cell in sheet1, with the other code Logaraj Sekar shared in this thread, that updates B2 cell in sheet2.
When I update D2 and D3 again cell in sheet1, with the other code Logaraj Sekar shared in this thread, that updates B3, B4, B5.. cell in sheet2 without overwrite.

But requirement is that When I update D2 cell in sheet1, It should also update A2 in Sheet2 with the cell value of A2 of Sheet 1 along with updating B2 cell in sheet2.
So, When I update D2 cell in sheet1, It should also update A2, A3, A4 in Sheet2 with the cell value of A2, A3, A4 of Sheet 1 respectively along with updating B2, B3, B4, B4 cell in sheet2 without overwrite.
wwoodin-2265
Copper Contributor
Oct 14, 2022
Here was 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