Running a macro every five minutes

A

ADE2

Hi

I need help to combine the two macros below.


Further down the line i will be hooking Excel up to a real time dat
feed for financial data.I would need to run a macro at five minut
intervals.

However i can't run it exactly on the 5 minute interval
eg:-18:55:00,19:00:00,19:05:00,becuse i need to run it maybe 20 second
after the five minute intervals to allow the data to be posted in th
data feeds database,eg:-18:55:20,19:00:20,19:05:20


The first macro below is for timing and the second macro AUTOINTRADA
is the macro i need to run every five minutes.

I'm not sure how to combine the two as i can't seem to get it to work.

Thanks for the ideas

Ade


Sub Every5()
'start the timer on a multiple of 5 minutes
ts = #1:00:00 PM#
dur = 5 * 60 'seconds 5 minutes
seq = 1
Do
DoEvents
this = (Timer - ts - 20) Mod dur
If this < last Then
MsgBox "FIRE"
End If
last = this
Loop
End Sub








Sub AUTOINTRADAY()
'
' AUTOINTRADAY Macro
' Macro recorded 27/05/2004 by ADE
'

'
Sheets("IMPORT").Select
Range("A1:F551").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending
Key2:=Range("B1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
DataOption2 _
:=xlSortNormal
Sheets("IMPORT").Select
Range("A2:F459").Select
ActiveWindow.SmallScroll Down:=-108
Selection.Copy
Range("B3:F730").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=-1
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("G5").Select
Sheets("FILTER").Select
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy
CriteriaRange:=Range( _
"Z2:AE3"), CopyToRange:=Range("Z4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=14
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy
CriteriaRange:=Range( _
"AF2:AK3"), CopyToRange:=Range("AF4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=18
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy
CriteriaRange:=Range( _
"AL2:AQ3"), CopyToRange:=Range("AL4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=-8
ActiveWindow.SmallScroll Down:=-6
Range("A3:E239").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-9
Range("A3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Range("G36").Select
Sheets("FILTER").Select
Range("I22").Select
ActiveWindow.SmallScroll ToRight:=18
ActiveWindow.SmallScroll Down:=-9
Range("AA5:AA58").Select
Selection.ClearContents
Range("AG5:AG29").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=7
Range("AM5:AM39").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-9
ActiveWindow.SmallScroll ToRight:=-3
Range("Z5:Z46").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-6
Range("H3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Range("M34").Select
Sheets("FILTER").Select
Range("AD5:AD46").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-6
Range("L3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Range("M34").Select
Sheets("FILTER").Select
Range("AF5:AF54").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=7
Range("O3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Sheets("FILTER").Select
Range("AJ5:AJ54").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=7
Range("S3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AL5").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=9
Range("V3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
_
IconFileName:=False
Range("AB29").Select
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AP5").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("AB29").Select
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=-2
Range("Z4:AP972").Select
Selection.Clear
Range("AQ5").Select
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=-16
ActiveWindow.SmallScroll Down:=-6
Range("F10").Select
Sheets("INTRADAY").Select
Range("P38:p41").Select
Selection.Copy
Range("Q38").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("V86").Select
Sheets("COPY SHEET").Select
Cells.Select
Selection.Clear
Selection.Delete Shift:=xlUp
Selection.ColumnWidth = 10
Range("I12").Select
Sheets("INTRADAY").Select
Range("V88").Select
Range("AF1").Select
Sheets("GBP INTRA").Select
Range("A1:AZ250").Select
Selection.Copy
Sheets("COPY SHEET").Select
Range("A1").Select
ActiveSheet.Paste
Range("G2").Select
Range("E3:E227").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BF3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("L3:L25").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BM3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("S3:S24").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BT3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("Z3:Z22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("CA3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("AG3:AG22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("CH3").Select
ActiveSheet.Paste
Range("CE27").Select
Sheets("GBP INTRA").Select
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("INTRADAY").Select
Range("AE4").Select
Sheets("COPY SHEET").Select
Range("E3:E22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("G3:G5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("G22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("G12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F9:G9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R38").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F15:G15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F12:G12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("L3:L22").Select
Application.CutCopyMode = False
Selection.Copy



If [B2] > [C5] And [B3] < [C7] Or _
[B2] < [C5] And [B3] > [C7] Then
xcount = 1
For xcount = 1 To 5
Beep
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Next



End If


End Sub
 
C

Chip Pearson

You can use the OnTime method to run a macro at a specified time.
See www.cpearson.com/excel/ontime.htm for details and example
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

ADE2 > said:
Hi

I need help to combine the two macros below.


Further down the line i will be hooking Excel up to a real time data
feed for financial data.I would need to run a macro at five minute
intervals.

However i can't run it exactly on the 5 minute intervals
eg:-18:55:00,19:00:00,19:05:00,becuse i need to run it maybe 20 seconds
after the five minute intervals to allow the data to be posted in the
data feeds database,eg:-18:55:20,19:00:20,19:05:20


The first macro below is for timing and the second macro AUTOINTRADAY
is the macro i need to run every five minutes.

I'm not sure how to combine the two as i can't seem to get it to work.

Thanks for the ideas

Ade


Sub Every5()
'start the timer on a multiple of 5 minutes
ts = #1:00:00 PM#
dur = 5 * 60 'seconds 5 minutes
seq = 1
Do
DoEvents
this = (Timer - ts - 20) Mod dur
If this < last Then
MsgBox "FIRE"
End If
last = this
Loop
End Sub








Sub AUTOINTRADAY()
'
' AUTOINTRADAY Macro
' Macro recorded 27/05/2004 by ADE
'

'
Sheets("IMPORT").Select
Range("A1:F551").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending,
Key2:=Range("B1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Sheets("IMPORT").Select
Range("A2:F459").Select
ActiveWindow.SmallScroll Down:=-108
Selection.Copy
Range("B3:F730").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=-1
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G5").Select
Sheets("FILTER").Select
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy,
CriteriaRange:=Range( _
"Z2:AE3"), CopyToRange:=Range("Z4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=14
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy,
CriteriaRange:=Range( _
"AF2:AK3"), CopyToRange:=Range("AF4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=18
Range("A2:E383").ADVANCEDFILTER Action:=xlFilterCopy,
CriteriaRange:=Range( _
"AL2:AQ3"), CopyToRange:=Range("AL4"), Unique:=False
ActiveWindow.SmallScroll ToRight:=-8
ActiveWindow.SmallScroll Down:=-6
Range("A3:E239").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-9
Range("A3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("G36").Select
Sheets("FILTER").Select
Range("I22").Select
ActiveWindow.SmallScroll ToRight:=18
ActiveWindow.SmallScroll Down:=-9
Range("AA5:AA58").Select
Selection.ClearContents
Range("AG5:AG29").Select
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=7
Range("AM5:AM39").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-9
ActiveWindow.SmallScroll ToRight:=-3
Range("Z5:Z46").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-6
Range("H3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("M34").Select
Sheets("FILTER").Select
Range("AD5:AD46").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll Down:=-6
Range("L3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("M34").Select
Sheets("FILTER").Select
Range("AF5:AF54").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=7
Range("O3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Sheets("FILTER").Select
Range("AJ5:AJ54").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=7
Range("S3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AL5").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=9
Range("V3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("AB29").Select
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AP5").Select
Selection.Copy
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("AB29").Select
Sheets("FILTER").Select
ActiveWindow.SmallScroll ToRight:=-2
Range("Z4:AP972").Select
Selection.Clear
Range("AQ5").Select
Sheets("GBP INTRA").Select
ActiveWindow.SmallScroll ToRight:=-16
ActiveWindow.SmallScroll Down:=-6
Range("F10").Select
Sheets("INTRADAY").Select
Range("P38:p41").Select
Selection.Copy
Range("Q38").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False,
_
IconFileName:=False
Range("V86").Select
Sheets("COPY SHEET").Select
Cells.Select
Selection.Clear
Selection.Delete Shift:=xlUp
Selection.ColumnWidth = 10
Range("I12").Select
Sheets("INTRADAY").Select
Range("V88").Select
Range("AF1").Select
Sheets("GBP INTRA").Select
Range("A1:AZ250").Select
Selection.Copy
Sheets("COPY SHEET").Select
Range("A1").Select
ActiveSheet.Paste
Range("G2").Select
Range("E3:E227").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BF3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("L3:L25").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BM3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("S3:S24").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("BT3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("Z3:Z22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("CA3").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("AG3:AG22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("CH3").Select
ActiveSheet.Paste
Range("CE27").Select
Sheets("GBP INTRA").Select
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("INTRADAY").Select
Range("AE4").Select
Sheets("COPY SHEET").Select
Range("E3:E22").Select
Selection.Copy
Sheets("INTRADAY").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("COPY SHEET").Select
Range("G3:G5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("G22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("G12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F9:G9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R38").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F15:G15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("F12:G12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INTRADAY").Select
Range("R36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("COPY SHEET").Select
Range("L3:L22").Select
Application.CutCopyMode = False
Selection.Copy



If [B2] > [C5] And [B3] < [C7] Or _
[B2] < [C5] And [B3] > [C7] Then
xcount = 1
For xcount = 1 To 5
Beep
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Next



End If


End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy & paste macro for multiple worksheets 8
Run-time error 1004 paste method of worksheet class failed 3
Macro to run on hidden sheet 4
Macro - Code Missing 4
Copying Form cells (WS1) to Database (WS2) 1
VBA Code- Copy & Paste in Blank Range 9
Run-time error "1004" Select method of range class failed 3
End Down a second time 1

Top