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
- 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 Subhow do you change the location for the master cell
and how to specifie vhen tu star something like i need to start on A4
what if you need more then one off these rows and more then one off the master cells 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,
Dear Sir,
I also want this type of solution. But this code does not work if a cell $B$2 contains formula which returns some values. So can you please suggest what type of modification required to this code so I can store each changes in cell B2 which contains formula.
Here is your code,
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 SubI have also try to search from internet, but it does not give me proper solution.
Thanks In Advance.
- sir if you found any solution please reply
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
a = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("A" & a) = Sheets("Sheet1").Range("B2").Formula
End If
End Sub
Hello Sir,
I'm using your code for copying an updated cell from one sheet to another sheet however, I would like to copy the entire row of the updated cell into another sheet. Please advise as to what modifications I need to copy and paste the entire row. The code right now only copies the updated cell.
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
ActiveCell.Activate
a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Cast Worked").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
Dear Sir,
Very Usefull! I would like to thank you.
Could I have aquestion?
If we like to save the changes of B2 (sheet 1) to Column A (sheet 2)
B3 (sheet 1) to Column B (sheet 2)
B4 (sheet 1) to Column C (sheet 2)
Each cell of column B at sheet 1 to store at Sheet 2 in column A,B,C....
Note: The First row at Sheet 2 will have headers
How can we change your code?
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!
Logaraj Sekarthe VBA code works perfect IF the data is inputted by hand, but if the data is changed by reference to another cell or workbook i.e. A1= sheet6 C1 and C1 changes the A1 value then it does - unfortunately!!! - not work. Rescue please!?
did you ever find a solution to this problem, I am also trying to work out the same thing.
Logaraj,
I have a similar problem as what you helped hopr37 with. I have a worksheet that keeps track of labor. When we take a new job, labor gets added to it, and when we complete portions of the labor, or ship a completed job, the labor comes off. What I'd like to do is keep track of labor as it happens. We run into a problem when we ship lets say a job that has 10 hours of labor on it, and then take on a job that has 10 hours of labor on it in the same day. The number changes, but changes back to the original amount, so we don't know how efficient jobs are moving through production. I'd like to record any increase changes to that number in one cell, and any decreases to that number in a different cell. For a twist, I want those changes to change throughout the week, as long as TODAY()'s date is before a date I have listed in another cell. Does this make sense? I'd rather not post the worksheet to the forum, but will send it to you directly.
Thank you
