Copy Record to Appropriate Worksheet Based on Contents of Column A - VBA.

[Si]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[Tom Ogilvy]

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).

 

[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

 

[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

 

[Tom Ogilvy]

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.

 

[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