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
Had to change a value and it's working. Thank you so much.
Now :)
I didn't want to look at too much of that code until I understand it however, is it now possible to have the same effect but instead of just changing one cell value it could do the same thing no matter what cell I change in column B?
assuming: If Target.Address = "$B$2"
means column B cell2, could this be changed so that any cell in column B could do the same thing and not just column B cell 2?
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
- 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- 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. - 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
Logaraj Sekar sir, if I want to stop the recording of data to sheet 2 at a certain row e.g. row 5 and continue recording automatically to a next column and end it again at row 5 and start next column and so on, how do i alter the code to do that or do i need to add a separate code?
Thanks
- Here was the 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
- can i update cell without using enter because my data is updated through links
That works great!
One question. Can I have it start at a particular cell?
It wants to start off by copying the headers I'm using and I need it to start at C4.
actually. After trying it, it seems to post the number above the cell I change.
So if C4= a value of 2 and I change that value, it copies value in C3 to the second worksheet.
so every cell I change on the first sheet copies the value from sheet 1 from the cell ABOVE the cell value I change.
does that make sense?
I think I got it...
hopr37 hotmail
1. This is answer for your first question.
Note: If you have any headers or any value in the range "C1,C2,C3", there is no problem. You can continue with the following. If not, put space in blank cells in C1,C2,C3.
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 Sub2. Just clarify the following.
a. So if you have value "2" in "Sheet2", this will result to bring above cell's value.
Eg. "Sheet2", "C6" value is 2, it will bring "C5" value.
"Sheet2", "C10" value is 2, it will bring "C9" value.
Right?
If this is not you want. Just explain me step by step. Put your questions one by one not in same paragraph. If you gave example, i will solve your question easily.
I have a Colom Containing only two Type of Values "Open" and "Closed" I want to have Current Date in the range next to the Selected range whenever i change a cell Value from Open to Closed or I enter Close in a Range
Can u Please help me in this Regard
