0

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.

Community
  • 1
  • 1
RedRen
  • 1
  • 2

1 Answers1

0

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
abdullah
  • 86
  • 1
  • 7
  • I just tried this entered, and it initially works great just makes one copy at the specific second, then a few seconds pass and it repeats itself and then once more. Do you have any thoughts as to how to stop it multi copying over a few seconds? – RedRen May 15 '20 at 16:22
  • As in maybe a way to get it to only run at that specific second and only once, thanks for the help so far – RedRen May 15 '20 at 16:24
  • Friendly hint: in nearly every case it's better to [avoid Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|198.5635) :-) – T.M. May 15 '20 at 16:27
  • thanks for the link to read through, I had a look but can't understand what the use of Select might have on the reason why the code is repeating a few times? – RedRen May 15 '20 at 16:45
  • so I followed the link you left and rewrote the code avoiding using Select but it still copies the cell multiple times – RedRen May 15 '20 at 17:16
  • change 2 in DATEADD function to 50 or 100 its the amount of time this Subroutine or any other Subroutine will not be triggered. As i mentioned before the problem is not in this macro its in the other macro which is calling this one – abdullah May 15 '20 at 19:06
  • abdullah thanks very much, it's definitely making progress - the date add function of the code, can you use that to outline how many seconds the code waits before it executes the function? So where you said you can change it to 5 seconds or whatever you want, is that 5 seconds it waits after the trigger has occurred before it functions the macro? – RedRen May 15 '20 at 20:06
  • no no it execute the code and then wait for 5 second before it exist That part which is commented as wait for 5 second is looping the code and doing nothing for 5 second – abdullah May 15 '20 at 20:55
  • Is it possible to use that time delay to trigger another macro. By this I mean could you specify that you take Time Now() then add say 5 seconds then call the next macro which then waits 5 seconds and calls the next macro etc. – RedRen May 16 '20 at 00:05
  • If you can use a 'Then Call' function within the code above, where would you place it? Could you place it under the Time=Date(Add.... line? – RedRen May 16 '20 at 00:06