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

kevinchurchill

New Member
Joined
Jul 20, 2015
Messages
26
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 OPR
Code article SAPNuméro de commandeDate d'envoi de la commande Délais de livraison prévu
700013205450045626612/01/201818/01/2018
<colgroup><col width="80" style="width: 60pt;" span="5"> <tbody> </tbody>

Date de la DDMdate de demande chiffrageRetour chiffrageDate d'envoi de la commande Numéro de commandeCode article SAPMontant Délais de livraison prévu Date Reception
01/01/201811/01/201811/01/201817/01/20189500303528 540,00 €24/01/201824/01/2018
01/01/201801/01/201801/01/201816/01/20189500303432 224,00 €23/01/201824/01/2018
01/01/201801/01/201801/01/201804/01/20189500303079 74,00 €10/01/201812/01/2018
01/01/201801/01/201801/01/201824/01/20184500457298 6 729,17 €06/02/201813/02/2018
01/01/201808/01/201808/01/201812/01/2018450045626670001320595,00 €18/01/201816/01/2018
01/01/201801/01/201805/01/201805/01/20184500455815700013746761,52 €18/01/201822/01/2018
03/01/201803/01/201812/01/201817/01/20189500303528 253,96 €24/01/201824/01/2018
03/01/201803/01/201803/01/201804/01/20189500303079 1 065,44 €25/01/201812/01/2018
03/01/201803/01/201803/01/201803/01/20189500303052 365,35 €03/01/201803/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>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

pmich

Active Member
Joined
Jun 25, 2013
Messages
282
Try This :
Code:
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
 
Last edited:

kevinchurchill

New Member
Joined
Jul 20, 2015
Messages
26
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

Active Member
Joined
Jun 25, 2013
Messages
282
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

New Member
Joined
Jul 20, 2015
Messages
26

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Kevin,

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

Code:
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.
 
Last edited:

kevinchurchill

New Member
Joined
Jul 20, 2015
Messages
26

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
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

Active Member
Joined
Jun 25, 2013
Messages
282
Here is a modified code given by vcoolio
Code:
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

New Member
Joined
Jul 20, 2015
Messages
26
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,700
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top