0

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 1

Pic 2 Pic 2

Gmaster
  • 30
  • 5
  • `.Selec` tells me that either you accidentally deleted a letter in pasting, or you'll have several other issues. First step is to remove all of the `.Select` and `.Selection`. Second, what does "it doesn't work" mean to you? Have you stepped through your code (F8) to see where you start seeing issues? – Cyril May 19 '22 at 12:13
  • Make sure you qualify all of your reference... none of your ranges have sheet references, so you may be pulling info from the incorrect sheet. Example, `Sheets(1).Range("A1").Value` will take the A1 value from sheet 1 while `Range("A1").Value` will take from the active sheet. – Cyril May 19 '22 at 12:14
  • As @Cyril said - also `Private Sub` instead of `rivate Sub`. And `xlPasteValuesAndNumberFormats` instead of `xlPasteValuesAndNumber Formats` and `Operation:= _` instead of `Operation:=_` for the line break. – Darren Bartrup-Cook May 19 '22 at 13:32
  • @DarrenBartrup-Cook. Dear, there is no mistake in my Macro5 as i didn't wrote code, i just recorded macro in excel, and some spelling mistakes you saw there are done by Editor, he changed somethings and I also found there are some mistakes made by me while copy pasting, but I don't have problem with macro, I can run it very well. But the major problem is ABOVE CODE that commands to run Macro. It runs perfectly in code 1, but not in 2. Macro runs perfectly when I manually press RUN to do, but don't it doesn't automatically. That's what i want to do. Thanks for your interest! – Gmaster May 21 '22 at 03:02
  • @Cyril , pls read my above msg written to Darren Bartrup-Cook. And yes i tried to see where is issue, but problem is nothing happens, no errors nothing when I make changes in cell to change formula Number. There is no chance of error in macro as I recorded it, i didn't wrote code manually, also When I Press RUN to execute my MACRO5, it runs perfectly. But when cell value changes nothing happens. – Gmaster May 21 '22 at 03:10
  • The above code sometimes Runs but it starts pasting randomly, means it works in loop, but sometimes only. **I have added below code too got from other discussion** Option Explicit Private Sub Worksheet_Calculate() Static Myoldval If Range ("L4").Value <> Myoldval Then Application.EnableEvents = False Call Macro5 Application.EnableEvents = True Myoldval = Range ("L4").Value End If End Sub – Gmaster May 21 '22 at 03:20

0 Answers0