Why does code1 work, and code 2 doesn't?
The only difference I see is Macro in code 2 is more complex.
What I wanted to do
Run advance macro automatically when cell value changes due to formula. The formula sums the data in L4 cell, from =J4+K4 (data in both these cells updates automatically as taken from another site)
A) VBA code 1 that works, but is simple
Option Explicit
Private Sub Worksheet_Calculate()
Static Myoldval
If Range ("L4").Value <> Myoldval Then
Call Macro5
Myoldval = Range ("L4").Value
End If
End Sub
Sub Macro5()
Dim lastrow As Long
Lastrow = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Sheet2.Range("A" & (lastrow + 1)) = Sheetl.Range("L4")
End Sub
B) VBA code 2, I'm using it but doesn't work, nothing happens when I do changes in cell
Option Explicit
Private Sub Worksheet_Calculate()
Static Myoldval
If Range ("L4").Value <> Myoldval Then
Call Macro5
Myoldval = Range ("L4").Value
End If
End Sub
' Macro start
Sub Macro5()
'
' Macro5 Macro
' Keyboard Shortcut: Ctrl+Shift+T
'
Range ("A1:01").Select
Selection. End (xlDown).Select
ActiveCell.offset (1, 0).Range ("A1").Select
Sheets ("Analysis").Select
Range ("I4:L4").Select
Selection.Copy
Sheets ("Record").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
What my macro do
It copies data from Pic 1 and upload to pic 2
Pic1
Pic 2
