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



## Latteeeee (Tuesday at 8:39 PM)

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.xlsmABCD1NameItemDate in Date out2Abc1391003-07-2304-01-233Asd1928304-07-2305-01-234Aft1421305-07-2306-01-235Ayz1391006-07-2307-01-236AhE1433107-07-2308-01-2378RawData RawData

DelMacro2.xlsmABCDE1Search13910<-- Item input cell23NameItemDate outDate in4567^Data from RawData expected to be populatedForm


----------



## kevin9999 (Tuesday at 9:00 PM)

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


----------



## Latteeeee (Tuesday at 9:04 PM)

kevin9999 said:


> 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 (Tuesday at 9:43 PM)

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 (Tuesday at 10:20 PM)

kevin9999 said:


> Try this on a *copy *of your data
> 
> ```
> Option Explicit
> ...


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 (Tuesday at 11:24 PM)

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 (Wednesday at 12:41 AM)

kevin9999 said:


> 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 (Wednesday at 1:20 AM)

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


Latteeeee said:


> 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 (Wednesday at 2:32 AM)

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.xlsmABCDEFGHIJKLMNOPQRSTUV1Overall credit statusSold-toShip-to name1Ship-toShip-to landCPNSales ProductFinished ProductAllocation PolicySales OrderDeliveryQty.Delivery Creation DatePlanned GI DateActual GI DateIncotermLast Leg Forwarder No.Last Leg Forwarder NameLast Leg AWB No.Sp DescriptionPlantPOD Date/Time(Last Leg)2A32141tomatoqwe1230139102SG8611200593SP0129310MA129301391AL119023910398123919309,000.00031-12-2209-01-23SAP1230912031SG0200-01-00 0:00Delivery

Del=Form
DelMacro.xlsmABCDEFGHIJ1CPN Search cell2DeliveryDelivery Creation DatePlanned GI DateActualy GI DateCPNQTYShip ToIncotermLast Leg Forwarder NameLast Leg AWB No3456789101112Del


----------



## kevin9999 (Wednesday at 3:44 AM)

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
```


----------



## Latteeeee (Tuesday at 8:39 PM)

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.xlsmABCD1NameItemDate in Date out2Abc1391003-07-2304-01-233Asd1928304-07-2305-01-234Aft1421305-07-2306-01-235Ayz1391006-07-2307-01-236AhE1433107-07-2308-01-2378RawData RawData

DelMacro2.xlsmABCDE1Search13910<-- Item input cell23NameItemDate outDate in4567^Data from RawData expected to be populatedForm


----------



## Latteeeee (Wednesday at 4:16 AM)

kevin9999 said:


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


The code works the first time when I run through the process. As I have another macro ("Import Button") that will allow me to import the raw data file first into Delivery sheet.
Upon the first search, it works
But as I copy/paste another search in, ran into a bug.
It seems the filtering is...making it harder? Do you know how to make the code reset Delivery sheet to its original state? perhaps that might resolve the issue. I'm not very sure how I can undo the filtering/reset the filter properly so delivery sheet returns to the original state
If I choose to not do the filtering/sorting, will it be simpler? cuz I can always add another button to sort afterwards.
Below is the mini sheet for what happens after the first search is completed.

DelMacro.xlsmABCDEFGHIJKLMNOPQRSTUV123Overall credit statusSold-toShip-to name1Ship-toShip-to landCPNSales ProductFinished ProductAllocation PolicySales OrderDeliveryQty.Delivery Creation DatePlanned GI DateActual GI DateIncotermLast Leg Forwarder No.Last Leg Forwarder NameLast Leg AWB No.Sp DescriptionPlantPOD Date/Time(Last Leg)Delivery


----------



## kevin9999 (Wednesday at 4:20 AM)

I really can't do much more without getting access to your actual file, including this other macro you're now mentioning. Could you share via Dropbox, Google Drive or similar, and I'll take another look at it tomorrow.


----------

