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?

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
(E-Mail Removed)
"Kitten74" <(E-Mail Removed)> wrote in message
news:5C086F85-C8B0-40C3-9BFD-(E-Mail Removed)...
>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?
>

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
> (E-Mail Removed)
> "Kitten74" <(E-Mail Removed)> wrote in message
> news:5C086F85-C8B0-40C3-9BFD-(E-Mail Removed)...
> >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
(E-Mail Removed)
"Kitten74" <(E-Mail Removed)> wrote in message
news:F305A376-F597-4028-A2B5-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Kitten74" <(E-Mail Removed)> wrote in message
>> news:5C086F85-C8B0-40C3-9BFD-(E-Mail Removed)...
>> >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?
>> >
>>
>> .
>>