Search copy paste from one sheet to another using only selected cells

[kevinchurchill]

Hello,


I have an inventory XL spread sheet, what I would like to do is search for a specific refference N°(code article SAP) that will be in column B sheet 1 and then copying only the data for that refference that is in rows, N,O,P,R. and then pasting that data to another sheet, i want this as the end user does not need to see the full data relevent to the refference N°.
So the first example below is what I want to end up with on another sheet after searching the first sheet using only the Code article SAP N°.
I hope this is clear.
Many thanks for your help
[TABLE="width: 300"]
<colgroup><col width="80" style="width: 60pt;" span="5"> <tbody>[TR]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"] N N [/TD]
[TD="width: 80, bgcolor: transparent"]O[/TD]
[TD="width: 80, bgcolor: transparent"]P[/TD]
[TD="width: 80, bgcolor: transparent"]R[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, width: 80, bgcolor: #92D050"]Code article SAP[/TD]
[TD="class: xl65, width: 80, bgcolor: #92D050"]Numéro de commande[/TD]
[TD="class: xl65, width: 80, bgcolor: #92D050"]Date d'envoi de la commande [/TD]
[TD="class: xl65, width: 80, bgcolor: #92D050"]Délais de livraison prévu [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: white, align: right"]700013205[/TD]
[TD="class: xl66, bgcolor: white, align: right"]4500456266[/TD]
[TD="class: xl67, bgcolor: white, align: right"]12/01/2018[/TD]
[TD="class: xl67, bgcolor: white, align: right"]18/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 912"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="145" style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;" span="5"> <tbody>[TR]
[TD="class: xl66, width: 111, bgcolor: #D9D9D9"]Date de la DDM[/TD]
[TD="class: xl66, width: 116, bgcolor: #D9D9D9"]date de demande chiffrage[/TD]
[TD="class: xl66, width: 127, bgcolor: #D9D9D9"]Retour chiffrage[/TD]
[TD="class: xl75, width: 136, bgcolor: #92D050"]Date d'envoi de la commande [/TD]
[TD="class: xl75, width: 145, bgcolor: #92D050"]Numéro de commande[/TD]
[TD="class: xl75, width: 145, bgcolor: #92D050"]Code article SAP[/TD]
[TD="class: xl66, width: 145, bgcolor: #D9D9D9"]Montant [/TD]
[TD="class: xl75, width: 145, bgcolor: #92D050"]Délais de livraison prévu [/TD]
[TD="class: xl66, width: 145, bgcolor: #D9D9D9"]Date Reception[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl70, width: 116, bgcolor: white"]11/01/2018[/TD]
[TD="class: xl70, width: 127, bgcolor: white"]11/01/2018[/TD]
[TD="class: xl76, width: 136, bgcolor: #92D050"]17/01/2018[/TD]
[TD="class: xl77, width: 145, bgcolor: #92D050"]9500303528[/TD]
[TD="class: xl78, width: 145, bgcolor: #92D050"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]540,00 €[/TD]
[TD="class: xl76, width: 145, bgcolor: #92D050"]24/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]24/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]16/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]9500303432[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]224,00 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]23/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]24/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]04/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]9500303079[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]74,00 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]10/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]12/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]24/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]4500457298[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]6 729,17 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]06/02/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]13/02/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]08/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]08/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]12/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]4500456266[/TD]
[TD="class: xl74, width: 145, bgcolor: red"]700013205[/TD]
[TD="class: xl67, width: 145, bgcolor: white"]95,00 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]18/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]16/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]01/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]05/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]05/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]4500455815[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]700013746[/TD]
[TD="class: xl67, width: 145, bgcolor: white"]761,52 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]18/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]22/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]12/01/2018[/TD]
[TD="class: xl72, width: 136, bgcolor: #92D050"]17/01/2018[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"]9500303528[/TD]
[TD="class: xl73, width: 145, bgcolor: #92D050"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]253,96 €[/TD]
[TD="class: xl72, width: 145, bgcolor: #92D050"]24/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]24/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 136, bgcolor: white"]04/01/2018[/TD]
[TD="class: xl69, width: 145, bgcolor: white"]9500303079[/TD]
[TD="class: xl71, width: 145, bgcolor: #E2EFDA"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]1 065,44 €[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]25/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]12/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 111, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 116, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 127, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 136, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl69, width: 145, bgcolor: white"]9500303052[/TD]
[TD="class: xl71, width: 145, bgcolor: #E2EFDA"] [/TD]
[TD="class: xl67, width: 145, bgcolor: white"]365,35 €[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]03/01/2018[/TD]
[TD="class: xl68, width: 145, bgcolor: white"]03/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[pmich]

Try This :

Private Sub cmdSearchTextInSh1AndPasteCellsInSh2_Click()
 Dim ws1 As Worksheet, ws2 As Worksheet
 Dim LastRecNum As Long
 Dim YourTxt As String
 Dim c As Range
 
 Set ws1 = Worksheets("Sheet1")
 Set ws2 = Worksheets("Sheet2")
 ws1.Select
 LastRecNum = Cells(Rows.Count, "A").End(xlUp).Row
 YourTxt = InputBox("Enter Text", "Your Text", "Type your Text here")
 For Each c In Range("a2:I" & Trim(Str(LastRecNum)))
  If c.Value = YourTxt Then
   c.Select
   ws2.Range("N2").Value = Selection.Value
   ws2.Range("O2").Value = Selection.Offset(0, -1).Value
   ws2.Range("P2").Value = Selection.Offset(0, -2).Value
   ws2.Range("R2").Value = Selection.Offset(0, 2).Value
   Exit For
  End If 'c.Value = YourTxt
 Next
 MsgBox "Programme over!" & vbCrLf & _
  "Click EXIT now!"
End Sub

 

[kevinchurchill]

Thank you for your response,
Unfortunately the code will not execute as a macro, although when I run the code from module 1 it executes but only revealing input box and exit box, there is no copy and paste.
Regards

 

[pmich]

Glad you have made a try.
The code works for me, not in the module but in the userform.
It shows only input box and not exit box.
Instead of using inputbox, you can store the value in the YourTxt variable in the code itself.
You say that there is no copy and paste.
What you want to copy and where you want to post?
If you be specific, either me or some expert will be able to help you.

 

[kevinchurchill]

O.K., What I want to achieve is, In sheet 1 I have data spread out over many columns (A through to AF) and rows 6 through to 1000.
Now on sheet 2 I want to create a search box that will search only column P on sheet 1 for any refference number that I input in the search box, when found I would like to copy only the cells in columns N,O,P and R. that are related to the refference number and paste these cells Under the same headings on sheet 2 as in the first picture above.
Then to finalise I would like the macro to reset the search box ready for the next search.
I hope this is clear for you, and thank you for your help.
Best regards.

 

[vcoolio]

Hello Kevin,

See if the following code does the task for you:-

Sub TransferData()

        Dim SAPSrch As String
        Dim lr As Long
        Dim cArr As Variant, pArr As Variant

SAPSrch = Sheet2.[A1].Value
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

cArr = Array("N2:N" & lr, "O2:O" & lr, "P2:P" & lr, "R2:R" & lr)
pArr = Array("N", "O", "P", "R")

Application.ScreenUpdating = False

Sheet1.[A1].CurrentRegion.AutoFilter 16, SAPSrch
For x = LBound(cArr) To UBound(cArr)
        Sheet1.Range(cArr(x)).Copy
        Sheet2.Range(pArr(x) & Rows.Count).End(3)(2).PasteSpecial xlValues
        Next x
        
Sheet1.[P1].AutoFilter
Sheet2.[A1].Value = "SAP Search"

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to a little mock-up I've prepared for you based on your explanations.

http://ge.tt/9ylYNpp2

Type an SAP number from Column P into the yellow search box then click on "GO".

I hope that this helps.

Cheerio,
vcoolio.

 

[kevinchurchill]

Hello Vcoolio,
Many thanks for the code, I can not see your example as it say's (share not available) when I click on the link.
I would very much like to see your workbook so that I have a better understanding of the code.
Best regards.
Kevin.

 

[vcoolio]

Hello Kevin,

Let's try again. Here's a new link to the sample file:-

http://ge.tt/7n8Gqpp2

If you intend to test the code in your workbook, make sure that you test it first in a copy of your workbook.

Cheerio,
vcoolio.

 

[pmich]

Here is a modified code given by vcoolio

Private Sub cmdTransferData_Click()
 'https://www.mrexcel.com/forum/excel-questions/1055270-search-copy-paste-one-sheet-another-using-only-selected-cells.html
        Dim SAPSrch As String
        Dim lr As Long
SAPSrch = Sheets("Sheet2").Range("A1").Value
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Sheet1.[A1].CurrentRegion.AutoFilter 16, SAPSrch '16 is column number
Worksheets("Sheet1").Range("N1:R" & lr).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Worksheets("Sheet2").Range("A2")
Sheet1.[P1].AutoFilter
Sheets("Sheet2").Range("A1").Value = SAPSrch
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[kevinchurchill]

Hello Vcoolio,
Thank you for the link, Ive been out of the office last week so I have tried your code today, you have given me all that I wanted except that it pastes the entire column and not just the cells related to the SAP N°.
Do you have a soloution for this.
Best regards.
Kevin.