Copy and Paste Values of one cell into another Macro

[starfox_64]

Here's the deal. When I click on the "Backlog" button, cell E27 changes to a new number. I want the value of E27 to be copied to cell E35 before it changes. And from there to E36 when I click on "Backlog" again, etc etc. This way I can see the historical trend as E27 changes throughout the day.

This is what I have so far, but it simply pastes E27 to E35 an infinite amount of times.

Sub ChartSectionCopy()
Dim i As Long

For i = 1 To Worksheets("Chart Names").Range("E27").Value
Range("27").Select
Selection.Copy
Range("E35").Offset(8, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next i

End Sub


Does anyone know how to fix this?

 

[LazyBug]

Without button.

Dim x As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E27")) Is Nothing Then Cells(Cells(34, 5).End(xlDown).Row + 1, 5).Value = x
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("E27")) Is Nothing Then x = Target.Value
End Sub