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

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

To be more specific:

Sub cpyYNT()
With Worksheets("Summary")
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 With
End Sub


"JLGWhiz" wrote:

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

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

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

Excel VBA-ers!

Thanks so much for your coding ideas and I've tried them all at work but
alas to no avail.

To further clarify what we're trying to do.

The user enters either Yes, No or Tentative into column A in the Summary
Worksheet, followed by the remainder of the record. As soon as the user
finishes entering a record we wish the data to be copied from the Summary
Worksheet to the appropriate Worksheet - i.e. to either Worksheet Yes, No or
Tentative!

I hear what you're saying about capturing the moment the user has finished
entering the record in Summary and I will capture this moment with a command
button once the code's in place.

We do *not* want to have the data in the Summary worksheet to be cleared
once the user has entered the data in Summary (and then have it copied to
the
appropriate Worksheet)

The VBA will have to enable new records to be added/ accommodated to the
Yes, No or Tentative worksheets without affecting (i.e. overwriting) the
existing records.

Tom's code at present copies the record from and only to the Active Sheet -
Summary. One record becomes 2 records, then if the macro is run again we
have 4 records, then 8, then 16 etc.

The 'real' work based workbook uses a couple of dozen worksheets but the
principle
will be exactly the same as the Summary, Yes, No, Tentative example.

Again any help you might be able to give would be greatly appreciated.

All the best,

Si

"Si" wrote in message
...
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

Tom's code at present copies the record from and only to the Active Sheet -
Summary.

Sorry, but this statement is asinine. Just to placate you, I tested the
code and it did exactly as it was designed to do. If it is copying to the
activesheet, you have changed the code. Here is the tested version copied
from the module.

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

However, this was not my code - I fixed JLGWhiz's code so it would operate
properly as he designed it.

I agree that it takes no cognizance of what is already on the other
sheets - - but then of course you had not yet included the information that
would indicated it would need to.


I will let you address that with him.

--
regards,
Tom Ogilvy


"Si" wrote in message
...
Excel VBA-ers!

Thanks so much for your coding ideas and I've tried them all at work but
alas to no avail.

To further clarify what we're trying to do.

The user enters either Yes, No or Tentative into column A in the Summary
Worksheet, followed by the remainder of the record. As soon as the user
finishes entering a record we wish the data to be copied from the Summary
Worksheet to the appropriate Worksheet - i.e. to either Worksheet Yes, No
or
Tentative!

I hear what you're saying about capturing the moment the user has finished
entering the record in Summary and I will capture this moment with a
command
button once the code's in place.

We do *not* want to have the data in the Summary worksheet to be cleared
once the user has entered the data in Summary (and then have it copied to
the
appropriate Worksheet)

The VBA will have to enable new records to be added/ accommodated to the
Yes, No or Tentative worksheets without affecting (i.e. overwriting) the
existing records.

Tom's code at present copies the record from and only to the Active
Sheet -
Summary. One record becomes 2 records, then if the macro is run again we
have 4 records, then 8, then 16 etc.

The 'real' work based workbook uses a couple of dozen worksheets but the
principle
will be exactly the same as the Summary, Yes, No, Tentative example.

Again any help you might be able to give would be greatly appreciated.

All the best,

Si

"Si" wrote in message
...
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