Forum Discussion
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 not only make a copy of it but would continue to make copies onto a different worksheet without overwriting itself?
example:
sheet 1
B2 has a value. I change the value. It copies the value to sheet 2. I then change the value on sheet 1 again. It again copies the value onto sheet 2 but it doesn't overwrite it, it just populates the next row number over.
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 I was wondering if this macro would work when I am trying to move a whole row from a table in sheet one to a table on sheet two If column B has a value of "Finished". So the clearify I have two sheets. and a table in each sheet = Sheet one with table one & sheet two with table two and if table one column B has value of FINISHED then that row moves to sheet two table two. Also the tables have hundreds of rows is there a certain way it has to be written to make sure it appears without over riding anything in the table its being added too?
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- Hi,
Hoping you can help me as I am at a real lose end and don't fully understand vb!
I have an excel to record current, historic and restated KPI figures so that we have an audit trail of all data ever reported. I want to use one tab (the current figures, which contains multiple tables of data) as the working tab so that everything else is automated and if we make a change to any cell within a particular range it will record the previous iteration in another tab (restated figures).
Example using a 4x4 'input' table:
- If the value in cell A1 changes, I need the previous figure to be captured on a separate tab in the 'output' table (eg cell Sheet 2, A1)
- If the value in the 'input table' cell A1 changes again, I need the previous figure to be captured to form a list in the 'outout' table A1 (e.g A1 now says 1, 2)
-If the value in the 'input table' cell B3 changes, same as above but to the corresponding cell in 'output table' eg. B3
Is it possible to contain the output value as a list in one cell, as opposed to along a row?
And is it possible to search a range (across a specific row and column) as opposed to a column?
Thanks - sir above code work perfectly when cell value changed manually but it is not working when cell value changed due to formula execution or refference cell value changed please help me sir
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.
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
anyone?
