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

JorgenKjer

Board Regular
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:

Fluff

MrExcel MVP, Moderator
Do you need code? This can easily be done with a formula. In A4 copied down & across
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">er</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;">< <<   search criteria </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;">Name</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;">Phone</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;">Employee #</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #C00000;;">Department</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;;">Peter Smith</td><td style="text-align: right;border-top: 1px solid black;;">24682345</td><td style="text-align: right;border-top: 1px solid black;;">2002</td><td style="border-top: 1px solid black;;">A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">John Fitzgerald Kennedy</td><td style="text-align: right;;">24683456</td><td style="text-align: right;;">3003</td><td style=";">A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Peter Jensen</td><td style="text-align: right;;">24684567</td><td style="text-align: right;;">4004</td><td style=";">A</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Report</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Data!A$2:A$5,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">Data!A$2:A$5</font>)-ROW(<font color="Teal">Data!A$2</font>)+1</font>)/(<font color="Purple">ISNUMBER(<font color="Teal">SEARCH(<font color="#FF00FF">$A$1,Data!$A$2:$A$5</font>)</font>)</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
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.
 

Some videos you may like

This Week's Hot Topics

Top