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
Dear Logaraj_Sekar,
I have noticed that you have helped someone with a similar problem to mine. I have used your code successfully. However, I would also like to add "and" function in my statement.You can see what I have done below (it is not currently working):
Private Sub Worksheet_Change(ByVal Target As Range)
If "B1" = "EURO" And Target.Address = "$B$2" Then
a = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet4").Range("A" & a).Value = Sheets("Sheet2").Range("B2").Value
Else
If "B1" = "USD" And Target.Address = "$B$2" Then
a = Sheets("Sheet4").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("Sheet4").Range("B" & a).Value = Sheets("Sheet2").Range("B2").Value
End If
End Sub
I would like to specify that if B1 contains the word "EURO" and the target address for the changing values is B2 from sheet 2, then I would like the values from B2 to be placed in column A in sheet 4, else if B1 contains the word "USD" and the target address for the changing values is B2 from sheet 2, I would like the values from B2 to be placed in column B in sheet 4.
Would you be able to help me with this issue?
Thank you
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" And Range("B1").value = "EURO" Then
a = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet4").Range("A" & a).Value = Sheets("Sheet2").Range("B2").Value
Else
a = Sheets("Sheet4").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("Sheet4").Range("B" & a).Value = Sheets("Sheet2").Range("B2").Value
End If
End SubLogaraj Sekar Hello , I see you helped many people , so I hope you can help me out as well...
I have a worksheet with products I got in offer listed on it, what I am trying to do now is when I type number of pcs in amount in sheet2 to automatically copy selected columns of that row to sheet1 on the list of offer and move selection on next row so I would need only to type amounts in sheet2 and then move to sheet1 where it would all be listed.
What I need is if value of cell H3 - H98 in sheet1 is higher than 0 to do following:
- Copy range B3-B98 from sheet2 to range B15-B53 on sheet1
- Copy range H3-H98 on sheet2 to E15-E53 on sheet1
- Copy range I3-I98 on sheet2 to F15-F53 on sheet1
(So just those mentioned rows where column H is higher than 0 should be copied and after coping to move to cell below where it will copy second product with amount higher than 0 again without moving to sheet1 until its all done)
Is this possible to be done?
I appologize if I was not clear as English is not my first language , so if something is not clear please let me know... And thanks for your help in advance!
Hi, Sorry now only i saw ur message.
If you already found solution, pls ignore this reply. If not, pls send sample worksheet.
