run my code in every 15 min

[Dipak543]

M not able to run this code in every 15 min ...someone pls help me ... the code start at 9;15 am and it's copy the value in every second from RTD data from Column 2 (in subsequent rows) but i want that the code copy data in every 15 min only. Suppose at 9:30 am the code starts and copy the value and then stop automatically its repeat the process in every 15 min till 15:30.

Private Sub Worksheet_Calculate()
If Time >= TimeSerial(15, 29, 59) And Time <= TimeSerial(15, 30, 0) Then

capturerow = 2

currow = Range("A65536").End(xlUp).Row

Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 3) = Cells(capturerow, 3)
Cells(currow + 1, 4) = Cells(capturerow, 4)
Cells(currow + 1, 5) = Cells(capturerow, 5)


Else
Exit Sub
End If

End Sub

 

[Dipak543]

I tried this but its not working

 

[Logit]

.

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 900    ' 1800 seconds = 30 min.   30 x 60 = 1800
Public Const cRunWhat = "Worksheet_Calculate"         ' the name of the procedure to run
Private TimerEnabled As Boolean             ' This value can only be changed by code in this module

Sub StartTimer()
    TimerEnabled = True
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Sub RunIt()
    Dim Crng As Range, Prng As Range
    Dim StartTime As String
    Dim StopTime As String
    Dim CurrentTime As String
    CurrentTime = Time
   
    StartTime = "9:30:00 AM"
    StopTime = "15:30:00 PM"
   
    If CurrentTime >= StartTime Then
        If CurrentTime >= StopTime Then
            Call StopTimer
        Else
            If TimerEnabled Then StartTimer
        End If
    Else
   
        'Call to YOUR MACRO goes here
        'Example : If you macro name is MyMacro ... then you would place Call MyMacro in this line
       
         Call  Worksheet_Calculate

        If TimerEnabled Then StartTimer
    End If
End Sub

Sub StopTimer()
    On Error Resume Next
    TimerEnabled = False
    Application.OnTime EarliestTime:=Now(), Procedure:=cRunWhat, Schedule:=False
End Sub

 

[Dipak543]

Thank you for your quick reply sir, actually, I am a newbie in VBA and I tried to use this code but it's showing an error, I attached the Photo pls

go through it

 

[Logit]

.
You'll need to move Sub Worksheet_Calculate() to a REGULAR MODULE and rename the macro to something else.

Also, the TIMER macro should be in a REGULAR MODULE as well.

You'll also need to remove the 'timer' portion of that macro ( If Time >= TimeSerial(15, 29, 59) And Time <= TimeSerial(15, 30, 0) Then ) since
the other timer macro is doing the time keeping now.

 

[Dipak543]

thank you so much logit finally it's worked...tomorrow I will check in live market

 

[Logit]

You are welcome.