Transfer from one worksheet to another with a codition

[Closed]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello, all
i have question I'm trying to populate a work book in order to organized my warehouse. on the first sheet i have 6 columns with the following titles :Qty, Unit, Part Number, Description, Serial No.,Box No. and what I'm trying to do is to have another sheet that would show the entire line of an item that would have a serial number.
is that possible ?
thank you
kostas



1 reply

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Kostas,

From what I understand, you have a long list of items and would like to search for a particular serial number for a stock item in a "Stock" sheet and have the full details of that item displayed in a separate sheet. If so, the following code may do the task for you:-

Sub FindStuff()

Application.ScreenUpdating = False

    Dim sNumSearch As String

sNumSearch = InputBox("Please enter the serial number to search for.")
If sNumSearch = vbNullString Then Exit Sub

With Sheets("Sheet1")
    .AutoFilterMode = False
    With Range("E2", Range("E" & Rows.Count).End(xlUp))
        .AutoFilter 1, sNumSearch
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
       .AutoFilterMode = False
End With

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Sheet2").Select

End Sub


The code uses an Input box (sheet1) in which you enter the serial number to be searched and when found, will transfer the whole row of data associated with that serial number to sheet 2.

I have attached my test work book for you to peruse here:-

https://www.dropbox.com/s/xdakxow7l4r6fct/Kostas.xlsm?dl=0

Click on the "Search Serial Nos." button to execute the code and bring up the input box. Enter a serial number from the fictitious list to have that row of data transferred to sheet 2.

I hope that this helps.

Cheerio,
vcoolio.