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

JorgenKjer

Board Regular
Joined
Aug 1, 2016
Messages
65
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Do you need code? This can easily be done with a formula. In A4 copied down & across

Book1
ABCD
1er< << search criteria
2
3NamePhoneEmployee #Department
4Peter Smith246823452002A
5John Fitzgerald Kennedy246834563003A
6Peter Jensen246845674004A
Report
Cell Formulas
RangeFormula
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))),"")
 
Upvote 0
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.
 
Upvote 0
Hi Fluff
Thanks for your prompt reply. The code works perfectly
Regards
Jorgen
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top