• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search and find rows and place in a separate tab

Rangerguy

New Member
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?Search and find select rows.png team selects
 

Attachments

Last edited:

AlanSidman

Well-Known Member
Code:
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

New Member
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

New Member
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

Excel Ninja
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

Well-Known Member
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.
 
Top