Copy is criteria met
 

Sub GETDATA()

With Sheet2.Range("a1:a200")
Set c = .Find(What:="FILE ID #", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
r = 2
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
With Sheet3
.Cells(r, 4) = c.Offset(10)
.Cells(r, 5) = c
End With
Set c = .FindNext(c)
r = r + 1

Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

"Kitten74" wrote in message
...
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?

Copy is criteria met
 

Hi Sorry, totally confused?

"Don Guillett" wrote:

Sub GETDATA()

With Sheet2.Range("a1:a200")
Set c = .Find(What:="FILE ID #", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
r = 2
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
With Sheet3
.Cells(r, 4) = c.Offset(10)
.Cells(r, 5) = c
End With
Set c = .FindNext(c)
r = r + 1

Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

"Kitten74" wrote in message
...
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?



.

Copy IF criteria met
 

"Don Guillett" wrote:

Sub GETDATA()

With Sheet2.Range("a1:a200")
Set c = .Find(What:="FILE ID #", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
r = 2
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
With Sheet3
.Cells(r, 4) = c.Offset(10)
.Cells(r, 5) = c
End With
Set c = .FindNext(c)
r = r + 1

Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

"Kitten74" wrote in message
...
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?



.