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

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

AliPEEM

Copper Contributor

May 20, 2020

Logaraj Sekar

Dear Friend,

First, thank you very much for your help.

I have got another problem I would like to get your help on, if possible.

I have a sheet that I have set it to be updated every 5 minutes from an XML file on the web, and after each update I want a particular row to be copied into a new row in another worksheet. The code you have introduced works perfectly when data is changed manually, but when it comes to my case, which is the sheet being updated automatically, it doesn't recognize each update as a worksheet change event, even though there are several changes.

I would appreciate it if you help me to address this problem. Thank you in advance.

Regards,