Copy Cells To New Table If Criteria Met

[thegooser1]

Hi all...

I have a few different systems which can be filtered / selected using the info in a spreadsheet with the auto market.

Does anyone know of any macro that can copy the info from a column into a table on another sheet if a cell in that column contains a specified value?

This is so I can paper trail these systems for a bit.

So for example, if AW10 = 1 then copy from A10 to AW10 and add it to a table on another sheet.

Then at the end of the days racing I can check the table which will have all the data in from the days races where the AW cell had a 1 in and therefore my selections.

I have no idea what to do with macros or VBA so could really do with some help.

Any ideas?

[thegooser1]

OK I have got this far:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("aw1").Column Then Exit Sub
Application.EnableEvents = False
If Target = 1 Then
Range(Cells(Target.Row, 1), Cells(Target.Row, "AX")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1#).PasteSpecial
End With
Else
Application.EnableEvents = True
Exit Sub

End If
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

The problem is it requires me to manually type the number 1 in the AW cells. What I need to do is trigger it if a criteria is met, then trigger it again if it changes back to "" and back to 1 again.

Any ideas?

[alrodopial]

I guess that in AW1 is your formulas that gives you 1 or 0 and want data to be copied only when the value goes from 0 to 1
Try this (not tested)

Code: Select all

Dim previousVALUE as integer
Private Sub Worksheet_Change(ByVal Target As Range)


If Range("AW1").Value = 1 and previousVALUE = 0 Then
Application.EnableEvents = False
Range(Cells(Target.Row, 1), Cells(Target.Row, "AX")).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1#).PasteSpecial
End With
Application.CutCopyMode = False
Application.EnableEvents = True
End If

previousVALUE = Range("AW1").Value

End Sub

[thegooser1]

Hiya... thanks for your help.

thats almost there.

The code you sent will only work on a1 and only copy the 1st row. Is there any way to modify this so that if AW7 for example chnges to 1 it would copy the 7th row?

Also, this is copying the formulas over to the new sheet, is it possible to copy the values?

Thanks for any help

[alrodopial]

In which cells do you have formulas that you want to trigger the copy-paste?
If eg cell AW5 becomes 1 you want row 5 to be copied or larger range?
Can two cells AW5,AW7 became 1 at the same time?
What do you want to happen then?

[thegooser1]

Hiya mate.

What I am really after is an excel sheet / bot that can connect to gruss.

I would like it so that I can run a portfolio of systems and selections so that (for now at least) I can put a date, system, and horse. I would want the sheet to place bets as the day goes along using the quick pick lists.

But, what I would also like it to do is to be able to run without placing actual money on if the system isn't performing well but to still record the price. This way I can monitor a system (daily list of horses) without having to actually bet.

So for example:

System A has been running for 90 days. I want to bet 1% of my system bankroll on each selection throughout the day. This can be done using this sheet: http://gruss-software.co.uk/forum/viewtopic.php?t=3557

However system B has only been running for 10 days and I just want to monitor it for now before deciding to place real money on it.

So when the selections come up I want it to copy the odds (say at 3 mins) so that I can test it out. After a while I will have enough data to see if the system is going to work or not.

System C is set to bet 1% of the bankroll unless the bankroll goes under £200, then it goes into test mode. For the sake of my example this will happen half way thorogh the day so it will bet as per usual, but when the ballance drops below £200 it will go back into test mode ad just copy the odds.

I appreciate that bugger all of this makes any sence, but I am hoping that someone has something that I can use.

I am half way there. I have a sheet which will look up my selections as and when they apear. It will record the odds at 3 mins before the off and add it to the selection. I then have to put the results in manually.

I know zero about VBA, but someone made it for me so I just used a few lookups etc to get it recording which system it belongs to.