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
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 Sub
I have also try to search from internet, but it does not give me proper solution.
Thanks In Advance.
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 IfI've solved my problem.
I tried this:
Sub CutPaste(myTarget As Range)
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
ActiveCell.Offset(1, 0).Select
End If
End SubBut this paste only to a single row each time.
- Just tell me the sheet names of your worksheet. And what things you want to do?
I need your help. I have modified your code to read from multiple columns in my sheets. I have 3 sheets - Jan 2020, Feb 2020 and Master.
In this case any change in C1:C100 and E1:E100 in Jan 2020 would be passed to Master sheet column B and C
Jan 2020
I have tried modifying your code but it doesn't work for what I need.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1:C100")) Is Nothing Then
updateDynamic (Target.Row)
End If
If Not Intersect(Target, Range("E1:E100")) Is Nothing Then
updateDynamic (Target.Row)
End If
End Sub
Private Sub updateDynamic(ByVal RowNum As Integer)
C = Sheets("Jan 2020").Cells(RowNum, "C")
E = Sheets("Jan 2020").Cells(RowNum, "E")
a = Sheets("Master").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("Master").Range("B" & a).Value = C
Sheets("Master").Range("C" & a).Value = E
End Sub
Master Tab
The idea is that at any point in time my data in Jan 2020 or Feb 2020 can be updated and each change is saved in Master sheet without overwriting.
It gets interesting because I am trying to also store the source (Jan 2020 or Feb 2020) so that I can find out where each row comes from.
Is it even possible to do this in Excel? Can you please help?
beetee2 ,
You have three sheets Jan 2020, Feb 2020, Master.
So whatever change you made in Columns C and E in sheets Jan & Feb 2020. you want to record that change in Master sheet. Right?
Yes that is the aim. for now i only have Jan 2020 and feb 2020 but i would create Mar 2020 all the way to Dec 2020 once i get the first 2 working.
I have enclosed my spreadsheet to show how it works currently.
Problem: how do i find where each row of data comes from (did it come from Jan 2020 or Feb 2020? so in my Master tab- Row 28 & 29 is from Feb 2020 and Row 19-27 is from Jan 2020)
How do I get rid of those blank cells i.e. Row 28 (so because we move from cell C to cell E, we have lots of blank cell in Master tab
Dear sir,Logaraj Sekar
Thanks for the reply, but this modified code is not working properly for me. Like counter is incremented by one only if i am going to edit target cell which is B2.
But here i want something different. Now in the attached photo, a formula is written in B2 whose value is changing based on G2. Now i am going to change G2 daily and based on its value, B2 will also change. So I want to record each updated value of B2 in any column like in A1,A2,A3 and so on without manually refreshing B2.
Again Thanks for the support.
hello sir,
here i have attached new image with different examples.
About the example :
In this case, current price which is cell F3 is going to change once every day. And my last price which is cell F2 will remain fixed. Now in B2 i have calculated simple difference between current price and last price (F3-F2).
Result:
Now what i want is whenever there is update in formula which is cell B2(which will update whenever cell F3 will change), I want to store each difference in cell B2 to cell B3,B4,B5 and so on.
Hope it will useful..
Thanks
