Forum Discussion
when cell value changes, copy the information to a new worksheet but also retain each change
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
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
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