Thread: Copy is criteria met

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?

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Kitten74" <Kitten74@discussions.microsoft.com> wrote in message
news:F305A376-F597-4028-A2B5-6BFFC5D180C2@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Kitten74" <Kitten74@discussions.microsoft.com> wrote in message
>> news:5C086F85-C8B0-40C3-9BFD-CE3837006546@microsoft.com...
>> >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?
>> >
>>
>> .
>>