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" <(E-Mail Removed)> wrote in message
news:955FA2C0-DF20-40CB-89C8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> news:955FA2C0-DF20-40CB-89C8-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
>> news:955FA2C0-DF20-40CB-89C8-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> >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" <(E-Mail Removed)> wrote in message
>>> news:955FA2C0-DF20-40CB-89C8-(E-Mail Removed)...
>>>> 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
>>>>>
>>>>>
>>>
>>>
>>
>

Thanks Tom and JLGWhiz,

And please accept my sincerest apologies for not being absolutely precise
about what I'm trying to do..... I hope I did not sound in any manner
pointed or ungrateful for the immense help you have both already given to
me - that was certainly not my intention. I'm very new to newsgroups and to
VBA and I'm sure my extreme ignorance is painfully obvious.

As per Tom's reply to me, if possible, would JLGWhiz be able to point me in
the right direction to ¨enable new records to be added/ accommodated to the
Yes, No or Tentative worksheets without affecting (i.e. overwriting) the
existing records which have been copied from Summary to the appropriate
sheet.¨

Finally I have copied my reply to the group to publically say thanks to you
both - Cheers!

Si

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> >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" <(E-Mail Removed)> wrote in message
>>>> news:955FA2C0-DF20-40CB-89C8-(E-Mail Removed)...
>>>>> 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
>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>
>
>