Many many thanks for your quick responses to my question 'JLGWhiz' and Tom -
I'm going to try it with our 'real' data this morning...

All the best

Si

"Tom Ogilvy" wrote in message
...
This might work:

Not as written I don't think. All your Cells(rows.count,1) code in the
copy commands refer to the active sheet rather than where you want to
paste the data. (Also, it is unlikely the sheet would be named with a
.xls extension - just a thought. )


Sub cpyYNT()
Dim Lr as Long, i as Long
Dim sh as Worksheet
Set sh = ActiveSheet
Lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If sh.cells(i, 1).Value = "Yes" Then
With Worksheets("Yes")
sh.Cells(i, 1).EntireRow.Copy _
.Cells(Rows.Count,1).End(xlUp)(2)
End With
ElseIf sh.cells(i, 1).Value = "No" Then
With Worksheets("No")
sh.Cells(i, 1).EntireRow.Copy _
.Cells(Rows.Count,1).End(xlUp)(2)
End With
ElseIf sh.cells(i, 1).Value = "Tentative" Then
With Worksheets("Tentative")
sh.Cells(i, 1).EntireRow.Copy _
.Cells(Rows.Count,1).End(xlUp)(2)
End With
End If
Next
End Sub

Code untested and may contain typos.

All that said, it sounds like the OP wants the record copied upon entry -
possibly using an event. This would be complex since it would be
difficult to tell when the User has completed entry of the record and is
ready for it to be transferred. Probably better to attach code to a
button and have the User "commit" the data (and then clear the row for the
next entry? the OP doesn't say).

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
This might work:
Sub cpyYNT()
Lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If cells(i, 1).Value = "Yes" Then
Cells(i, 1).EntireRow.Copy Worksheets _
("Yes.xls").Range("A" & Cells(Rows.Count, _
1).End(xlUp).Row +1)
ElseIf cells(i, 1).Value = "No" Then
Cells(i, 1).EntireRow.Copy Worksheets _
("No.xls").Range("A" & Cells(Rows.Count, _
1).End(xlUp).Row +1)
End If
Next
End Sub

"Si" wrote:

I wonder if anyone can help me with the correct VBA to copy a record
from
one worksheet to another based on the contents of Column A in the input
operators worksheet, which I'll call Summary.

For example I have four worksheets - the aforementioned Summary, Yes, No
and
Tentative - each has a dozen fields and comprise identical fields.

The user inputs data into Column A in Summary only - either Yes, No or
Tentative then enters the rest of the record data.

If the user enters 'Yes' in the Summary worksheet I wish the whole of
the
record to be copied to the 'Yes' worksheet. If they enter 'No' I wish
the
whole record to be copied to the No worksheet etc.

I hope this makes sense and I'd really appreciate any help you might be
able
to give me.

All the best,

Si