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

[JorgenKjer]

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.

Thanks inadvance
Regards
Jorgen

 

[JoeMo]

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-board/508133-attachments.html

 

[JorgenKjer]

Hi Joe

I have tried to make a link to a small example.
Hope the link works

Jorgen

https://www.dropbox.com/s/zr2rgdafqj3o7a2/Copy and paste if criteria is partial met.xlsm?dl=0

https://www.dropbox.com/h?preview=Copy+and+paste+if+criteria+is+partial+met.xlsm

https://www.dropbox.com/sh/t3lr38mn75q2epo/AAAAurPfLOAjp1cEu0CQXL-pa?dl=0

 

[Fluff]

Do you need code? This can easily be done with a formula. In A4 copied down & across

Book1
	A	B	C	D
1	er	< << search criteria
2
3	Name	Phone	Employee #	Department
4	Peter Smith	24682345	2002	A
5	John Fitzgerald Kennedy	24683456	3003	A
6	Peter Jensen	24684567	4004	A
Report

Cell Formulas
Range	Formula
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))),"")

 

[Fluff]

Or if you do want a macro, try

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.

 

[JorgenKjer]

Hi Fluff
Thanks for your prompt reply. The code works perfectly
Regards
Jorgen

 

[Fluff]

You're welcome & thanks for the feedback