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