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

Iron Contributor

Apr 17, 2018

Hi hopr37 hotmail,

Assume you have 2 sheets like (Sheet1, Sheet2).

1. Right click the Sheet1 tab -> Click View Code

2. Paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & a).Value = Sheets("Sheet1").Range("B2").Value
End If
End Sub

arhunter1450

Copper Contributor

Jan 22, 2021

Logaraj Sekar

The code works however if B2 = another cell e.g B2 = C2, when changing C2, it doesn't create a new cell in sheet 2 unless you click on B2 then hit enter.

Is there anyway to change this? So that if B2 is referencing another cell it will work without having to click into B2 and manually hit enter?

Assume you have 2 sheets like (Sheet1, Sheet2).

1. Right click the Sheet1 tab -> Click View Code

2. Paste the following.