when cell value changes, copy the information to a new worksheet but also retain each change

[hopr37 hotmail]

Hello.

How would I copy a cell ( or row actually) to a new worksheet when I make a change to a particular cell in the first worksheet? AND whenever I make a change to that particular cell it would not only make a copy of it but would continue to make copies onto a different worksheet without overwriting itself?

example:

sheet 1

B2 has a value. I change the value. It copies the value to sheet 2. I then change the value on sheet 1 again. It again copies the value onto sheet 2 but it doesn't overwrite it, it just populates the next row number over.

[#2]

@Logaraj Sekar  , is there a way to do this or should I search for some other solution to handle this?

[#3]

I solved that problem with mixing many formulas I found on internet.

Thanks for effort...

[#4]

@Logaraj Sekar 

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?

[#5]

@Logaraj Sekar 

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.

[#6]

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

[#7]

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.

[#8]

@Logaraj Sekar 

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?

[#9]

@Logaraj Sekar 

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

[#10]

@joe_phinazee 

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 Sub

But this paste only to a single row each time.

[#11]

@yagi0 ,

I understood your problem 75%. just show me one example to solve.

[#12]

@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?

[#13]

@Logaraj Sekar 

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

[#14]

@beetee2 

try to add this line in respective month.

Sheets("Master").Range("A" & a).Value = "Feb 2020"

[#15]

Just tell me the sheet names of your worksheet. And what things you want to do?

[#16]

@Logaraj Sekar 

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

[#17]

I tried that but no success yet. Is there a way you can take a look at my file to see how it currently works?
Plus we are copying 2 columns, C1:100 and E1:100 from 1 tab to Master.

Please advise

[#18]

@joe_phinazee 

I've solved my problem.

[#19]

@joe_phinazee 

what was the solution?

[#20]

@HugoSpeciale 

did you ever find a solution to this problem, I am also trying to work out the same thing.

[#21]

@yagi0 

So the next day 1-may will be the last price and 2-may will be the current price.

So in "Cell B3" difference between 2-may and 1-may will be reflected. right?