Copy row to another sheet if cell contains a 0

[aiwnjoo]

Is there an easy way to automate if a cell in Column O for example contains a 0 then it highlights the entire row and pastes it into the next available row in another sheet lets call this Zeros.
Ive tried using the recorder but it gets funky.

Thanks,

 

[aiwnjoo]

bump.

 

[Jon von der Heyden]

Perhaps column A isn't empty? Try;

[COLOR=#0000ff]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change([COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range)
    [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] Intersect(Target, Columns("O")) [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
        [COLOR=blue]If[/COLOR] Target.Value = 0 [COLOR=blue]Then[/COLOR]
            Intersect(Target.EntireRow, Columns("A:V")).Copy
            Sheets("Zeros").Range("[B]O[/B]" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
            Application.CutCopyMode = [COLOR=blue]False[/COLOR]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Also you really should allow a little more time between bumps. And please don't send me chasers on PM. I'm very busy and only get to check the board a few times each day. This is a public forum so lets keep this chat here please. Thanks.

 

[aiwnjoo]

The other macro was right, it only pasted on row 2 each time i put a 0 in the other sheet where this macro is.

Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change([COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range)
    [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] Intersect(Target, Columns("O")) [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
        [COLOR=blue]If[/COLOR] Target.Value = 0 [COLOR=blue]Then[/COLOR]
            Intersect(Target.EntireRow, Columns("A:V")).Copy
            Sheets("Zeros").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
            Application.CutCopyMode = [COLOR=blue]False[/COLOR]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub

Example;

In the sheet containing this Macro i enter data in ABCDEFGHIJKLMN and when i enter a 0 in O i want to to copy that full row (A:V) and paste into the Zeros sheet.
In Row 1 of each of these sheets are cell headers so in that respect they are occupied so this macro;

Actually works fine, only problem being that i i enter a 0 in O on Row 23 and then on Row 24 it will paste over itself in the Zeros sheet instead of pasting on the next row down.

Thanks.

 

[aiwnjoo]

Bump.

 

[Jon von der Heyden]

Did you try the suggestion in post 12? It works for me.

 

[aiwnjoo]

Yeh i tried that one and it seemed to work ok however;

http://img293.imageshack.us/img293/3235/exampleg.jpg

As you can see Sheet 1 is where the Macro is, i have typed a 0 is O2 and it pasted over into the wrong place on the Zeros sheet, so i went to Sheet 1 and put another 0 in O3 and it pasted right over the previous in Zeros.

Thanks for replying.

 

[aiwnjoo]

Bump

 

[aiwnjoo]

bump

 

[aiwnjoo]

This might help you Jon

   Dim LR As Long
   LR = Sheets("Zeros").Cells(Rows.Count, "A").End(xlUp).Row + 1

.
.
.....
            Sheets("Zeros").Range("A" & LR).PasteSpecial xlValues
            Application.CutCopyMode = False
...

 

[aiwnjoo]

bump