Search and find rows and place in a separate tab

[Rangerguy]

Good afternoon. I have long order form with 300+ items. After my sales team selects items on the first tab from the "Qty Ordered" column, I would like to have a "condensed" order form on a second tab that only shows the rows with items that they have selected. In other words, only rows that have a quantity > 0 or blank, would appear in the second tab. So if the customer only orders 8 items, the "condensed" order form only shows those 8 items. Can some advise me on the best formula to make this happen? team selects

 

[AlanSidman]

Sub OrderForm()
    Application.ScreenUpdating = False
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr As Long, lr2 As Long
    Dim i As Long
    Set s1 = Sheets("Order Form")
    Set s2 = Sheets("Condensed Order Form")
    lr = s1.Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lr
        If s1.Range("C" & i) <> "" Then
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s1.Range("B" & i & ":" & "D" & i).Copy
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Completed"
End Sub

 

[Rangerguy]

Thanks, Alan. Question for you. Is there a way to have the macro on tab 2 ("condensed order form") automatically update in live time if there are changes to any cell in the first tab(order form)? Meaning if C3(deep fry Thermometer on tab 1 goes from blank to 4) then the output on tab 2 automatically reflects those changes?

 

[Rangerguy]

As I think about the scope of how this document will be used, I will eventually be handing off this project to a peer and the Excel file may require changes down the road. I know that they are not familiar with even basic VB coding. Is there a non-VB formula that I could possibly use that would work as well?

 

[Marc L]

Hi, you can use an easy Excel basics : an advanced filter !​

​

In an automatic way using the same feature within a VBA procedure just needs a couple of codelines …​

​

 

[p45cal]

If you have the function FILTER available as part of a cell formula you could put something along the lines of:
=FILTER('Order Form'!B2:D183,NOT(ISBLANK('Order Form'!C2:$C$183)),"Nothing")
on the sheet for a condensed order form. This will update by itself when values on the first sheet change.

 

[Rangerguy]

Thank you Alan, Marc L, and p45cal for your assistance! Greatly appreciated!