Copy and Paste Code

[thegooser1]

Could someone have a look at the following code for me please?

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 idea is that I have a sheet with live data in and I want it to record the row if the cell in AW changes to 1.

For example, if AW8 changes to the number 1, then it copys the whole row (or at least A8:AW8) and pastes it into a table on a new sheet.

The problem I have is that it only works if I manually type the number 1 into AW cells. I need it to work on a criteria (for example in AW2 I put in =if c1<5,1,0) so that if it matched the criteria it records the row A2:AW2 on the new sheet.

If the cell changed back to blank, and then later on in the day it changed to a 1 again, I need that to be copied in again on a new row.

This is really baking my noodle and I'm really lost with all of this. I got the code above from someone who doesn't know how to get these alterations done so I wonder if you guys can help.

Also..... just to make it more complicated and confusing, when it does copy and paste them into the new sheet, it pastes the formulas in. Is this possible to paste the values in?

Any help really would be appreciated :)

[p45cal]

1. What cell(s) are being changed on the sheet (Columnn C?)
2. How are they being changed?

[thegooser1]

1. What cell(s) are being changed on the sheet (Columnn C?)
2. How are they being changed?

Hi there!

I was just using C as an example. If the AW cell changes to 1 then I need it recorded. It's all live data so I need to record it if and when it reaches certain criteria. What I do is do a load of calculations along the sheet and have it put a 1 in the AW column if I want it recorded.

If it changes back to a blank cell then later on in the day it might change again to a 1 so I'll need that record saved then as well.

Do you know where i can go from here?

[p45cal]

Again:
1. What cell(s) are being changed on the sheet?
2. How are they being changed?
ie. how is this a 'live' sheet and what sparks off all the calculations?

[thegooser1]

Again:
ie. how is this a 'live' sheet?

Hi, I left that out so I didn't complicate things too much...

I use a program which links data from a betting exchange to excel.

It scrolls through the next race for the day, all day every day giving lots of details. I have a few formulas running, (for example if the horses age is less than 3 years older than the youngest) and if all the criteria are met then a number 1 apears in the coresponding AW cell.

What I am trying to do is record the row the moment that all the criteria are met and the 1 apears in AW so I can see odds and horses that were selected at the end of the day.

Infact, hopefully, it would add all of the selections into a table that I can look at in a months time and see if selecting horses using the set criterias would be profitable.

[p45cal]

You've already noted that the change event doesn't respond to a change in a formula's result. If you want to keep using the change event I want to know which cells are changing and how. How is the 'program' putting the data into the sheet? Macro? DDE? some other way? So please:

1. What cell(s) are being changed on the sheet?
2. How are they being changed?

[thegooser1]

A1 to U30 are being constantly updated every second. I don't know the method that they are being populated and changed. From my end it's a program linking the data to excel. I link my sheet to the program and it populates the excel sheet with the market info.

V1 to AV30 are being changed by formulas calculating things from A1 to U30.

I know this doesn't really help though.

[p45cal]

Since:
I don't know the method that they are being populated and changed.,let's experiment to see whether the change event can be used. Change your change event to simply:Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub
Now go back to your updating sheet. Does it throw up a message box every second or so? If so, post here a sample of the cell addresses it's reporting.

[thegooser1]

Hiya Mate....


it throws up the following message: $A$1:$p$12 with a "OK" button. Pressing that brings up a message: $T$5:$AI$12 with another "OK" button.

Pressing it repeats the process.

[p45cal]

So it looks like it updates two different portions of the sheet alternately; A1:P12 and T5:AI12. Perhaps doing one immediately after the other, waiting a second, then repeating. Could you change the macro to:
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address(0, 0) & ", " & Timer
End Sub
then after a few seconds you should see some data appearing in the Immediate pane of the vb editor (Ctrl+G if you can't see it). It might look like this:
A1:P12, 55853.59
T5:AI12, 55853.79

but rather more entries. Could you copy and paste here 20 or so consecutive lines of this?

[thegooser1]

hi....

A1:P32, 56844.34
T5:AI32, 56844.35
A1:P32, 56845.35
T5:AI32, 56845.36
A1:P32, 56846.36
T5:AI32, 56846.37
A1:P32, 56847.38
T5:AI32, 56847.39
A1:P32, 56848.39
T5:AI32, 56848.4
A1:P32, 56849.41
T5:AI32, 56849.43
A1:P32, 56850.43
T5:AI32, 56850.44
A1:P32, 56867.98
T5:AI32, 56868
A1:P32, 56868.68
T5:AI32, 56868.69
A1:P32, 56869.69
T5:AI32, 56869.7

[p45cal]

..from which it's clear that the T5:AI32 range is being updated a hundredth of a second after the other region. So we'll use that second range to trigger doing something. I have to go out now for a while, but on return, I'll put something together which will remember AW1:AW32 between runs, and compare the new with the old, and where it has changed from anything but 1, to 1, copy the row(s) across to the other sheet. Please confirm
1. rows 1 to 32 are the only rows you want to monitor in column AW
2. whether there could be more than one row turning to a 1 at the same time.

[thegooser1]

Hi... yes rows 1 to 32 are the only cells. There could possibly be more than one cell changing to 1 at the same time.

Thanks for all your help mate.

[p45cal]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Static xxx
If Not Intersect(Target, Range("T5:AI32")) Is Nothing Then
If IsEmpty(xxx) Then
xxx = Range("Aw1:AW32").Value
Else
yyy = Range("Aw1:AW32").Value
For i = 1 To 32
If yyy(i, 1) = 1 And xxx(i, 1) <> 1 Then
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 50).Value = Cells(i, 1).Resize(, 50).Value
End If
Next i
xxx = yyy
End If
End If
End Sub
It will not copy across those rows where there is a 1 in column AW before you start - it looks for changes to a 1

[thegooser1]

Am am right clicking sheet1, going to view code, then patsing that code in. I can't get it to do anything though.

Am I applying it correctly here?

[p45cal]

Am am right clicking sheet1, going to view code, then patsing that code in. I can't get it to do anything though.

Am I applying it correctly here? The code is to replace the code in msg#s 1, 8 or 10. Same location.
It may not do much until one or more of the cells in AW1:AW32 changes from something that is not a 1, to a 1. If you do this manually in column AW, nothing will happen, because the code waits for changes/updates to cells in T5:AI32 before it does anything. So to test it, first get the sheet updating itself, then change any cell in the range AW1:AW32 which was not a 1, to a 1, let the sheet update itself, look at sheet2.

If you think this is not working, then put the edit cursor on the first line (the one which starts 'Private Sub..') of the macro and press F9 on the keyboard to put a break point in, when the code gets triggered it will stop at that line, highlighting it in yellow. This will tell you at least that it's being triggered. Thereafter, you can step line by line through the code with repeated pressing of F8, or let it carry on by itself by pressing F5. Don't forget to remove the break point by putting the cursor on that first line again and pressing F9 once more to toggle off the break point.

[thegooser1]

Hiya...

that code works perfectly. This is a big help to me and for the record, I posted this everywhere and nobody could really get this done.

Thanks so much for your help !!!