VB help needed - copying bet refs and odds

[Captain Sensible]

Can anyone post some example of how I'd copy details of my bets to say sheet 2. I'm useless at VB and can't seem to figure it out using either

Private Sub Worksheet_Calculate()

or

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

The cell values (some - odds) are generated by a formula so no idea if thats the reason why I'm having problems .

Thanks

[Mitch]

It sounds simple, but when I think about it it's not (unless someone else who knows more about vb than me can help).

The problem is triggering the routine as you seem to have found, because you need to know which cells to copy to the other sheet, and the cells aren't changing because they are formulae.

If you had vb triggering the bets it would be simple. If you want to go down that route I'd be glad to help with that if you need it.

[GeorgeUK]

The Worksheet_Change executes when the user or VBA modifies a cell, not when a formula is calculated.

This leaves you with Worksheet_Calculate if you want the event to fire when the formula changes.

This is where the format of your workbook will come in to play.

You could have:

Code: Select all

Private Sub Worksheet_Calculate()
'when column N(5-27) <> HOLD or CLEAR - place into Diary
Dim trigger As Range
Dim i As Range

Set trigger = Sheets("Sheet1").Range("N5:N27")
lastrow = Sheets("Sheet`").Range("a65536").End(xlUp).Row + 1

For Each i In trigger
    If i.Value <> "HOLD" And i.Value <> "CLEAR" Then
        Range("O" & lastrow & ":V" & lastrow).Value = Range("O" & i.Row & ":V" & i.Row).Value
        Range("G" & lastrow & ":N" & lastrow).Value = Range("D" & i.Row & ":K" & i.Row).Value
        Range("E" & lastrow & ":F" & lastrow).Value = Range("C2:D2").Value
        Range("D" & lastrow).Value = Range("B3").Value
        Range("C" & lastrow).Value = Range("B2").Value
        Range("B" & lastrow).Value = Range("A" & i.Row).Value
        Range("A" & lastrow).Value = Range("A1").Value
    End If
Next i

End Sub

This will copy the race and bet data to the bottom of the sheet.
It will keep listing this bet unless you add another line so that if there is a bet reference, it should stop.
But if you are putting multiple bets on various selections, your formula should change back to HOLD or whatever so that the selection is not continually copied down the page.

This is some code i put together for Thunderfoot when he asked a similar question.

It may perhaps be easier to have the code looking at column Q and when it is not PENDING, ERROR or "" (Blank) then it copies the line.

You should be able to adjust the code to paste to a second worksheet, but just holler if you have any difficulty.

[Captain Sensible]

Thanks Mitch and George, busy days racing - I'll check it out later see if I can make head or tail of it .

Thanks again