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

	N N	O	P	R
	Code article SAP	Numéro de commande	Date d'envoi de la commande	Délais de livraison prévu
	700013205	4500456266	12/01/2018	18/01/2018

<colgroup><col width="80" style="width: 60pt;" span="5"> <tbody> </tbody>

Date de la DDM	date de demande chiffrage	Retour chiffrage	Date d'envoi de la commande	Numéro de commande	Code article SAP	Montant	Délais de livraison prévu	Date Reception
01/01/2018	11/01/2018	11/01/2018	17/01/2018	9500303528		540,00 €	24/01/2018	24/01/2018
01/01/2018	01/01/2018	01/01/2018	16/01/2018	9500303432		224,00 €	23/01/2018	24/01/2018
01/01/2018	01/01/2018	01/01/2018	04/01/2018	9500303079		74,00 €	10/01/2018	12/01/2018
01/01/2018	01/01/2018	01/01/2018	24/01/2018	4500457298		6 729,17 €	06/02/2018	13/02/2018
01/01/2018	08/01/2018	08/01/2018	12/01/2018	4500456266	700013205	95,00 €	18/01/2018	16/01/2018
01/01/2018	01/01/2018	05/01/2018	05/01/2018	4500455815	700013746	761,52 €	18/01/2018	22/01/2018
03/01/2018	03/01/2018	12/01/2018	17/01/2018	9500303528		253,96 €	24/01/2018	24/01/2018
03/01/2018	03/01/2018	03/01/2018	04/01/2018	9500303079		1 065,44 €	25/01/2018	12/01/2018
03/01/2018	03/01/2018	03/01/2018	03/01/2018	9500303052		365,35 €	03/01/2018	03/01/2018

<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> </tbody>

 

[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.