Simple Macro Not Firing

[rob51852]

Hi guys,

I am trying to write a macro to copy/paste cells when a formula in AC5 = 60.

I have put this code in the worksheet:

Private Sub Worksheet_Calculate()
If Range("AC5") = "60" Then Call Capture
End Sub

And this code in a module:

Sub Capture()
   Application.ScreenUpdating = False
   
   With ThisWorkbook.Worksheets("Market").Range("Y5:Y34").Copy
      ThisWorkbook.Worksheets("Market").Range("Z5").PasteSpecial Paste:=xlPasteValues
   End With
   
   Application.CutCopyMode = False

   Application.ScreenUpdating = True
End Sub

But it is not firing when AC5 = 60.

Can anyone see what I'm doing wrong?

Thanks

 

[jmacleary]

Hi there. Try removing the quotes - as written it is looking for text.

       Private Sub Worksheet_Calculate()
If Range("AC5") = 60 Then Call Capture
End Sub

 

[rob51852]

Doh! Thanks John.

 

[jmacleary]

You're welcome and thanks for the feedback. My brain works (or doesn't) like that a lot of the time!