dell_12345
New Member
- Joined
- May 22, 2017
- Messages
- 16
Hi, Iv got some very basic VBA coding that will copy a cell value to another cell every time the original cell value changes. However this only works when I manually change the cell value. I want this to change automatically as it is linked to another sheet which is changing constantly. Below is my code:
People are saying use the Worksheet_Calculate function but I have no idea how to implement this in the above code.
Greatful for any help!!!!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim vaData() As Variant
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
ReDim vaData(1 To 1, 1 To 3)
vaData(1, 1) = Now()
vaData(1, 2) = Target.Address
vaData(1, 3) = Target.Resize(1, 1).Value
Application.EnableEvents = False
With Sheets("Sheet2")
lRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & lRow & ":C" & lRow).Value = vaData
End With
Application.EnableEvents = True
End Sub
People are saying use the Worksheet_Calculate function but I have no idea how to implement this in the above code.
Greatful for any help!!!!