VBA code needed for Copy/Paste SheetA to SheetB upon search input

[Latteeeee]

very new to VBA but I need a macro that 1)searches for a specific input 2)goes through SheetA(raw data) through the same column to search for input 3) recognize the input search and 4) copies the entire row into SheetB
Conditions:
1) Raw data file will always be different, but the column that input is searched upon will always be the same
2) There might be multiple results for the same search, meaning multiple rows, but each rows will contain varying data
3) Able to not repeat the copy/paste from SheetA to SheetB and not miss out on any rows/searches

*Unsure if xlookup is the way to go in the VBA code
Will really appreciate any help I can get!!

DelMacro2.xlsm
	A	B	C	D
1	Name	Item	Date in	Date out
2	Abc	13910	03-07-23	04-01-23
3	Asd	19283	04-07-23	05-01-23
4	Aft	14213	05-07-23	06-01-23
5	Ayz	13910	06-07-23	07-01-23
6	AhE	14331	07-07-23	08-01-23
7
8	RawData
RawData

DelMacro2.xlsm
	A	B	C	D	E
1	Search	13910	<-- Item input cell
2
3	Name	Item	Date out	Date in
4
5
6
7	^Data from RawData expected to be populated
Form

 

[kevin9999]

Are the Date in and Date out columns meant to swap around (as your example indicates)?

 

[Latteeeee]

Are the Date in and Date out columns meant to swap around (as your example indicates)?

Yes. Ideally I'd like to be able to arrange the columns.

 

[kevin9999]

Try this on a copy of your data

Option Explicit
Sub DelMacro2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("RawData")
    Set ws2 = Worksheets("Form")
    Dim myInput As String
    myInput = ws2.Range("B1").Value2
   
    'Clear existing data on Form sheet
    ws2.Range("A3:D" & Cells(Rows.Count, "D").End(xlUp).Row).Offset(1).ClearContents
   
    'Copy (then delete?) any data that matches
    With ws1.Cells(1, 1).CurrentRegion
        .AutoFilter 2, myInput
        If ws1.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1, 2).Copy ws2.Range("A4")
            .Offset(1, 2).Resize(.Rows.Count - 1, 1).Copy ws2.Range("D4")
            .Offset(1, 3).Resize(.Rows.Count - 1, 1).Copy ws2.Range("C4")
            '.Offset(1).EntireRow.Delete   '<<*** uncomment this if you want to delete the copied data
        Else
            MsgBox "No Items match " & myInput & " - exiting sub"
            .AutoFilter
            Exit Sub
        End If
        .AutoFilter
    End With
End Sub

 

[Latteeeee]

Try this on a copy of your data

Option Explicit
Sub DelMacro2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("RawData")
    Set ws2 = Worksheets("Form")
    Dim myInput As String
    myInput = ws2.Range("B1").Value2
  
    'Clear existing data on Form sheet
    ws2.Range("A3:D" & Cells(Rows.Count, "D").End(xlUp).Row).Offset(1).ClearContents
  
    'Copy (then delete?) any data that matches
    With ws1.Cells(1, 1).CurrentRegion
        .AutoFilter 2, myInput
        If ws1.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1, 2).Copy ws2.Range("A4")
            .Offset(1, 2).Resize(.Rows.Count - 1, 1).Copy ws2.Range("D4")
            .Offset(1, 3).Resize(.Rows.Count - 1, 1).Copy ws2.Range("C4")
            '.Offset(1).EntireRow.Delete   '<<*** uncomment this if you want to delete the copied data
        Else
            MsgBox "No Items match " & myInput & " - exiting sub"
            .AutoFilter
            Exit Sub
        End If
        .AutoFilter
    End With
End Sub

Thank you! It worked wonderfully for the sample data. For my actual application, I have a set of raw data that always changes in terms of rows generated, but the "searched" cell will always be under the same column, and my objective is to be able to search for different "searched cell", and have the data populated in "Form". So whenever I change the input in the searched cell and run the macro, it always repopulate to display only the rows I need.
Can you point me in the right direction?

 

[kevin9999]

The code in post #5 uses an AutoFilter on column B - so it will always only grab the rows that match your input value on the Form sheet.

 

[Latteeeee]

The code in post #5 uses an AutoFilter on column B - so it will always only grab the rows that match your input value on the Form sheet.

The actual data the input is from is actually Col F, can you tell me which part of the code I have to edit for it to search the input data under Col F?
Edit: I just changed the Autofilter 2 to 6 (which is the col I need it to be) and it generated the data I want.

I will work on editing the code accordingly to populate more data that I seek.
Can I just confirm with you that if I add in more [.Offset(1, 3).Resize(.Rows.Count - 1, 1).Copy ws2.Range("C2")] accordingly, it should display the remaining col from the actual raw data?
But thank you very much! You've really helped me a lot!

 

[kevin9999]

Glad I was able to help. With regard to this:

Can I just confirm with you that if I add in more [.Offset(1, 3).Resize(.Rows.Count - 1, 1).Copy ws2.Range("C2")] accordingly, it should display the remaining col from the actual raw data?

I'm afraid that I don't quite follow your meaning. The code already has an .Offset(1,3) - which is moving the focus to column D in the "RawData" sheet. If you mean there are more columns you want to copy from the "RawData" sheet to the "Form" sheet (columns to the right of D) then yes, you're on the right track, but you don't necessarily have to do one column at a time if the columns are contiguous and you don't want the orders changed. If you could provide a before-and-after example using the XL2BB add in then perhaps I could help?

 

[Latteeeee]

Yes that is what I meant.
Below I've provided a sample of the actual data I'm working with.
So my obj is that whenever I input the "CPN" I wish to search for, the macro will search off Col F in the raw data sheet, and copy/paste the data to the "Form" sheet with the newly arranged order. And each time I search, all data in the Form sheet (apart from headings and the rest) will be deleted. So like it renews and shows only the CPN related data I searched for.
In a way, I am also plucking specific columns of data that I want, to be pasted in the order that I want from raw data sheet to form sheet.

Delivery = Raw Data

DelMacro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Overall credit status	Sold-to	Ship-to name1	Ship-to	Ship-to land	CPN	Sales Product	Finished Product	Allocation Policy	Sales Order	Delivery	Qty.	Delivery Creation Date	Planned GI Date	Actual GI Date	Incoterm	Last Leg Forwarder No.	Last Leg Forwarder Name	Last Leg AWB No.	Sp Description	Plant	POD Date/Time(Last Leg)
2	A	32141	tomatoqwe	1230139102	SG	8611200593	SP0129310	MA129301391	AL	1190239103	9812391930	9,000.000	31-12-22	09-01-23		SAP				1230912031	SG02	00-01-00 0:00
Delivery

Del=Form

DelMacro.xlsm
	A	B	C	D	E	F	G	H	I	J
1					CPN Search cell
2	Delivery	Delivery Creation Date	Planned GI Date	Actualy GI Date	CPN	QTY	Ship To	Incoterm	Last Leg Forwarder Name	Last Leg AWB No
3
4
5
6
7
8
9
10
11
12
Del

 

[kevin9999]

That's quite a jumble of the order
Personally, I would use a custom sort, left to right, to get all the columns in the right order before the copy. I think I've got the order right - try (again) on a copy of your actual data. Copy all of the code below to a standard mode (uncomment the delete line if you want to delete chosen data after the copy).

Option Explicit
Dim ws1 As Worksheet, ws2 As Worksheet
Sub DelMacro3()
    Application.ScreenUpdating = False
    Set ws1 = Worksheets("Delivery")
    Set ws2 = Worksheets("Del")
    Dim myInput As String, x As String
    myInput = ws2.Range("E1").Value2
    x = "X"
    
    'Clear existing data on Form sheet
    ws2.Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).ClearContents
   
    'Copy (then delete?) any data that matches
    With ws1.Cells(1, 1).CurrentRegion
        .AutoFilter 6, myInput
        If ws1.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Range("A1:A2").EntireRow.Insert
            .Range("A1:V1").Offset(-2).Value2 = Evaluate("Column(" & .Address & ")")
            .Range("A1:V1").Offset(-1).Resize(1, 22).Value2 = Array(x, x, x, 7, x, _
            5, x, x, x, x, 1, 6, 2, 3, 4, 8, x, 9, 10, x, x, x)
            Sort_New_Order
            .Offset(1).Resize(.Rows.Count - 1, 10).Copy ws2.Range("A3")
            
            '.Offset(1).EntireRow.Delete   '<<*** uncomment this if you want to delete the copied data
            
            Sort_Old_Order
        Else
            MsgBox "No Items match " & myInput & " - exiting sub"
            .AutoFilter
            Exit Sub
        End If
        .AutoFilter
    End With
    ws2.Activate
    Application.ScreenUpdating = True
End Sub
Sub Sort_New_Order()
    ws1.Activate
    With ws1
        .Sort.SortFields.Clear
        .Cells(1, 1).CurrentRegion.Sort Key1:=Rows(2), Order1:=xlAscending, Orientation:=xlLeftToRight
    End With
End Sub
Sub Sort_Old_Order()
    With ws1
        .Sort.SortFields.Clear
        .Cells(1, 1).CurrentRegion.Sort Key1:=Rows(1), Order1:=xlAscending, Orientation:=xlLeftToRight
        .Rows("1:2").Delete
    End With
End Sub