Copy and Paste if Value changes

[JNEWSOME001]

I am new to this and have a question. I have a workbook that I am working on. On the sheet "Scores" the range Q109 show the date, range Q107 shows a numerical value. If the date changes and the value in Q107 also changes I would like the new date and value to copy/paste into another sheet called "Changes", the date would paste into cell B5 and the new value for range Q107 would paste into the Changes worksheet cell "C4". Thank you in advance for any help!!!

[Bob Phillips]

Do you really mean both changing, or just either one? I ask because it is difficult to track two cells for changes, you would save the new value of say cell A once it changes and then when cell B changes, cell A is still the same as its previous value (is that clear?). If it is either, or just one, worksheet change events can manage this very easily.

[JNEWSOME001]

Do you really mean both changing, or just either one? I ask because it is difficult to track two cells for changes, you would save the new value of say cell A once it changes and then when cell B changes, cell A is still the same as its previous value (is that clear?). If it is either, or just one, worksheet change events can manage this very easily.


If the date changes then the value will also change so I would like to be able to copy both the new date and the new value to the "Change" worksheet

[Bob Phillips]

Are you saying that there is a formula in the value that is driven by the date?

[JNEWSOME001]

Are you saying that there is a formula in the value that is driven by the date?


Yes, the value in Q107 is an average of scores, for example 95%, so if on March 1st 2014 the person's score was 98% and a new score was taken on April 1st 2014 and their new score was 95%, i would like the old date of March 1st to copy and paste into the Change worksheet into Cell B4 and the new date of April 1st to copy into the cell below it (B5), the old score of 98% would copy and paste into the Change worksheet C4, and the new score of 95% would copy into the row below it into cell C5, i am ok with the vba to copy and paste the cells but I am struggling with a code to only copy and paste when changes occur in the specified cells

[david000]

Post a sample workbook or explain what sort of date interval is required. Are you only updating the workbook once a month on the first? Are you using a volatile function like; =DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(1))? Could you use a Form Control like a Combobox and trigger an event, or is that out of the question?

[Bob Phillips]

Add this code to the worksheet code module for the Scores worksheet


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Cells.Count = 1 Then

If Not Intersect(Target, Me.Range("Q109")) Is Nothing Then

Worksheets("Changes").Range("B5").Value = Target.Value
Worksheets("Changes").Range("C4").Value = Me.Range("Q107").Value
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

[JNEWSOME001]

Add this code to the worksheet code module for the Scores worksheet


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Target.Cells.Count = 1 Then

If Not Intersect(Target, Me.Range("Q109")) Is Nothing Then

Worksheets("Changes").Range("B5").Value = Target.Value
Worksheets("Changes").Range("C4").Value = Me.Range("Q107").Value
End If




I tweaked it a little bit and it works beautifully :) Thank you so much, the help is greatly appreciated!!
End If

ws_exit:
Application.EnableEvents = True
End Sub