Copy is criteria met

[Kitten74]

I am having trouble finding a function that will copy the data in a given row
to another worksheet if one of the columns contains the word Yes.

For example if in the below situation cell G5 has a “Y,†I want to Create
Cover Sheet in Sheet 2 with return the data from row 5.
Sheet 1
Box/Packet No Item No Account Name Account Number Content Vault sheet Create
Cover Sheet
100 1 Mr A 123 AF 1 N
101 2 Mr B 456 AF 2 N
203 3 Mr C 789 AF 3 N
100 4 Mr D 111 AF 4 Y

Sheet2
Box/Packet No 100
Item No 4
Account Name Mr D
Account Number 111
Content AF
Vault sheet 4

Can someone help?

 

[Don Guillett]

One instance only or All if more than one found
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Kitten74]

Hi Thanks for helping, basically, I have a list i have to maintain, but
everytime something is added to the list i also need to complete a summary
sheet that goes in an archive box. I thought rather having to type out all
the information on to the list and the summary, it maybe possible to upadate
the list and this auto populate the summary sheet.

 

[Don Guillett]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Column <> 7 Then Exit Sub
If UCase(Left(Target, 1)) = "Y" Then
Rows(Target.Row).Copy
Sheets("Summary").Range("b3").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End If
End Sub