# Thread: A tuff one, Copy and paste if criteria is partial met Thanks: 0 Likes: 0

1. ## A tuff one, Copy and paste if criteria is partial met

Hi

Is itpossible to create a code that can copy rows from a data sheet and paste to areport sheet if there is a partial match?

Data is a staff record with data in 11 columns and about 450 rows. In column 1 (A) are names with first- middle- and last name in the same cell. For example, if you are looking for John, all persons with first name John should be copied and paste to the report sheet, the same e.g. last name Smith.

For each new search, the previous search in the report sheet must be deleted.

The data inthe report sheet should start from row 3 and Down

I would appreciate if anyone can help me.

Regards
Jorgen

2. ## Re: A tuff one, Copy and paste if criteria is partial met

What you are asking for is possible. You can improve your chances of getting some help by posting some sample data in a form that can be copied from a browser and pasted to Excel. See this link for how to do this: https://www.mrexcel.com/forum/about-...tachments.html

4. ## Re: A tuff one, Copy and paste if criteria is partial met

Do you need code? This can easily be done with a formula. In A4 copied down & across
ABCD
1er< <<   search criteria
2
3NamePhoneEmployee #Department
4Peter Smith246823452002A
5John Fitzgerald Kennedy246834563003A
6Peter Jensen246845674004A

Report

Worksheet Formulas
CellFormula
A4=IFERROR(INDEX(Data!A\$2:A\$5,AGGREGATE(15,6,(ROW(Data!A\$2:A\$5)-ROW(Data!A\$2)+1)/(ISNUMBER(SEARCH(\$A\$1,Data!\$A\$2:\$A\$5))),ROWS(\$A\$1:\$A1))),"")

5. ## Re: A tuff one, Copy and paste if criteria is partial met

Or if you do want a macro, try
Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Range("A3").CurrentRegion.Offset(1).ClearContents
With Sheets("Data")
.Range("A1:D1").AutoFilter 1, "*" & Target & "*"
.AutoFilter.Range.Offset(1).Copy Me.Range("A4")
.AutoFilterMode = False
End With
End If
End Sub```
This needs to go in the Report sheets code module.

Hi Fluff
Regards
Jorgen

7. ## Re: A tuff one, Copy and paste if criteria is partial met

You're welcome & thanks for the feedback