Search, filter and populate spreadsheet

[Bevilready]

Hello, I have a spreadsheet in which I would like to be able to use a search box to filter through a list of products and add a quantity to each item I search for. After which I'm would like the items I have put quantities against to appear in a separate list, which would become an order to send to my supplier. I have been able to create a search, but am not able to put a quantity in the cell which remains once the search is cleared.
Can anyone help me please?
Thanks,
Bevilready

 

[mumps]

Place this macro in a regular module and assign it to the "Re-Set" button.

Sub ReSet()
    Range("A4").AutoFilter
    Range("P5", Range("X" & Rows.Count).End(xlUp)).ClearContents
End Sub

 

[Bevilready]

Hi mumps, I've realised that after searching and adding a quantity to a product and it successfully appearing in the order form. If you repeat this process the second selection overwrites the first in the order form. It doesn't add the line beneath the first.
Would you be able to help please?

 

[mumps]

Each time you add a quantity for an item, the data is copied to the first blank row in the order form so I'm not sure what you mean by:

If you repeat this process the second selection overwrites the first in the order form. It doesn't add the line beneath the first.

Please clarify in detail, step by step, using a few examples.

 

[Bevilready]

Ignore me, I've been using the re-set button wrongly. I only need to press the re-set button when I've made all my selections.
I have found that the new script for the re-set button deletes the content from the headers on the order form though.

I would like to be able to clear the contents from the other table when I've placed the order.

 

[mumps]

Please note that this line of code clears the data starting at P5 (row 5). The headers are in row 4 so they shouldn't be deleted.

Range("P5", Range("X" & Rows.Count).End(xlUp)).ClearContents

Is the order considered placed when you click the "Re-Set" button? Do you want to clear all the data in columns C to G?

 

[Bevilready]

Yes the order is considered placed when I click the Re-Set button. But I would like to clear the data in columns E to G

 

[mumps]

Replace the previous macro with this one:

Sub ReSet()
    Range("A4").AutoFilter
    Range("P5", Range("X" & Rows.Count).End(xlUp)).ClearContents
    Range("E5:G" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End Sub

 

[Bevilready]

I think I've broke it...... Would you be able to get it working again?
It doesn't add any lines to the order form now.

Bevilready.xlsmxfyal9dv51dg4l8hxsia

 

[Bevilready]

Hi mumps, are you still with me? ?

 

[mumps]

When the data in the products chart are filtered, the data in the order chart are also filtered. This can't be avoided. Because some of the rows in the order chart are not visible, this creates problems for the macro when trying to paste the data in the first available row. The only way to fix this is to place the order data on a separate sheet. Click here for your file and give it a try.