Looping Advanced Filter specific to current worksheet

[RINCONPAUL]

I have a workbook that is fed horse race data and prices from a 3rd party software. The 3rd party software selects the races and loads them to the workbook 1 minute out from the official race starting time. The current race data is imported into sheet "BOTT". Each race has a specific number of runners, and I'm only interested in fields of 5....13 runners. In the workbook there are field size specific sheets called "Interface5".....13 and past data sheets, similarly "Data5"....13.

Each one of these "Interface" sheets is an Advanced Filter and becomes active if the current race displayed in sheet "BOTT" matches it's field size number. Let's say there is a 7 runner race current, therefore "Interface7 is being populated with ever changing prices. At the official start time for that race I manually trigger a macro button on the "Interface7" sheet called "FilterMe7". The advanced filter does it's thing, and references current prices to the attached "Data7" sheet and returns a betting strategy. I then press macro button "Clearme7" on the "Interface" sheet. The code for those two macro buttons is below. Each "Interface" sheet has a unique code for those two buttons per sheet.

The purpose of this post is to ask the question, "Is there a way to have the Advanced Filter macro updating (triggering) every second while that particular interface sheet is current and active?" This would save me having to manually trigger the event and would be useful as many races (especially USA) over run their start times, so the betting strategy can change markedly, from official start to actual start time. The macro would have to clear the sheet and be dormant when another race is loaded on the "BOTT" sheet, setting off another advanced filter loop.


Sub FilterMe7()
'
' FilterMe7 Macro
'
'
Sheets("DATA7").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("U10:AH11"), CopyToRange:=Range("A13:Q13"), Unique:= _
False
End Sub

Sub Clearme7()
'
' Clearme Macro
'
'
Range("A14:Q10000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub

[mdmackillop]

Here's a simple timer you could try. Don't know about the interface with the import. how does this work?


Sub Tim()
Dim t
t = Timer
Do
DoEvents
Loop Until Timer - t > 1
Call Test
If Cells(1, 3) = "x" Then Exit Sub
'or
If Cells(1, 1) > 100 Then Exit Sub
Call Tim
End Sub


Sub Test()
Cells(1, 1) = Cells(1, 1) + 1
End Sub

[RINCONPAUL]

Thankyou for your interest as always, mdmackillop. On each "Interface" sheet, in the prices input cells, there is a simple '=IF(BOTT!$CO$43=7,BOTT!CQ2,"")'. In other words when the current race field size number matches the Interface sheet field size, it will display the prices on that sheet, otherwise "".

Not sure how your timer interfaces with the sub Filterme5....13 ?? as in how does it trigger the macro Filterme 5...13, or is the text "Test" in your code, to be substituted with the particular trigger macro? Your code would have to reference back to BOTT!$CO$43, to say when this doesn't display 7 anymore, stop triggering Advanced Filter in sheet "Interface7", and then, let's say the next race has 8 runners and $CO$43 now displays 8, then sheet Interface8 Advanced Filter trigger "Filterme8" is triggered and keeps triggering every second until $CO$43 changes again. Then there's the matter of clearing the sheet with "Clearme5....13" on the changeover?....not easy??....for the likes of me anyway!!

[mdmackillop]

Here's a simulation to try

[mdmackillop]

Also
Change your Clear routines to avoid selection

Sub Clearme7()
Dim Rng As Range
Set Rng = Sheets("Whatever").Range("A14:Q10000")
Range(Rng, Rng.End(xlDown)).ClearContents
End Sub

[RINCONPAUL]

Sorry mdmackillop, just after my last post, I was called away unexpectantly interstate for a couple of days, and just back.

Looking at your simulation, I get how it works in that a change in the value at CO43 will call the appropriate Filterme macro and on another change clear the sheet before moving to the next macro. That's great! However, as I understand your code it only calls the Filterme macro once a change in CO43 appears (could be wrong)? But it only calls it ONCE? My needs were:, "Is there a way to have the Advanced Filter macro updating (triggering) every second while that particular interface sheet is current and active."

So I need it to call the Filterme macro every second. Hopefully I'm wrong with my assessment, but its hard to tell when the simulation macro is to start a clock ticking. Sorry to be a pain.:omg2:

[mdmackillop]

The macro can be started and stopped in number of ways, Manually, Worksheet event, OnTime Macro, Workbook event etc.
The FilterMe macros show the time at which the macro is triggered by the looping code. Try stepping through the code using the F8 key.

[RINCONPAUL]

Been fumbling my way through, trying to adapt your code to something close to the actual setup I have. The attached workbook has cover sheet BOTT wherein cell CO43 generates a random integer between 5....9. Then there are the attached sheets in order from 5...9. The FilterMe macro is a simple copy paste & sum, which should be repeating every second, until a change to CO43. The result reports back to the BOTT sheet. A runtime error exists when I try and run it, so I've stuffed something up? Where have I gone wrong mdmackillop?
Cheers

[mdmackillop]

You cannot select a cell on another sheet and there is no need to do so. Refer to the range directly complete with the sheet reference.

Sub FilterMe7()
Sheets("Interface7").Range("A1").Copy
Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("F1").FormulaR1C1 = "=RC[-4]+RC[-1]"
End Sub

[RINCONPAUL]

Finally implemented a real time solution, using a timer sub in cahoots with the actual "do something" sub. One pair per sheet. Thanks for all your support and assistance mdmackillop.