Stop an Excel macro, triggered by the value of another cell changing, from being fired multiple times,

Question

My macro essentially copies the value in B2 down to the next empty cell in column B.

A clock on another sheet, at a specific time, triggers the macro for one second only.

I was using a copy and paste macro. At the exact second the trigger equaled the specified time, the macro fired multiple times, usually 4-6 times. I want only one copy of the cell.

I changed the way I copied the value but the same issue occurred. This is the code below;

Sub Macro5()

Sheets("Sheet1").Select
Range("B6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Value = Range("B2")

End Sub

How do I stop my macro from copying the value in B2 multiple times in that one second?

EDIT

This is the code I used originally.

Sub Macro5()

Range("B2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
End Sub

I used a copy and paste values function.

The macro was triggered, to work on Sheet1, by the value of another cell changing on Sheet2. That cell's value was the result of a formula so Sheet2 had to be a Calculate event worksheet.

Sheet2 doesn't have any event types on it, just macros running specified to run on Sheet2.

no both codes were done on calculate worksheets. I think I could create a change event though if you had an idea how to fix the multi copying on that? — RedRen

abdullah abdullah · Accepted Answer · 2020-05-15T16:00:30

The problem is not in this macro rather the one calling it

Sub Macro5()
 dim time as date
 time = Now()
 'you can change time from 5 seconds to more or less
 time = (DateAdd("s", 5, time))
 Sheets("Sheet1").Select
 Range("B6").Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(1, 0).Value = Range("B2")


' for 5 seconds do nothing
DoEvents
Do Until time < Now()
Loop

End Sub

Stop an Excel macro, triggered by the value of another cell changing, from being fired multiple times,

1 Answers