How to take non zero values from a cell/column and place in another location

[robski]

Hi all, new user, would welcome some help. I have just started a job in sales and the company is quite archaic in its order process.

I work with 2 product catalogues, both of which I have spreadsheeets with costs on and i have added drop downs for the amount of each item required.

What I would like to do, as the sheets are quite long (4 and 8 pages repsectively) and there are lots of products and product codes is create a spreadsheet that copies the value of an entire line if the value of 1 of the boxes in the line is >0 i.e if someone orders 6 separate products, then rather than having to print out the whole order form for just 6 products, the formula/program with copy the values of the 6 product lines so I can create/print a small speciifc order.

I hope this makes sense.

Its a very basic initial sheet that has

Code Item Price Amount Total in £
XXX Hat £2.00 (dropdown 0to500) £x

accross the top and then a long list of products all the values in the 'Amount' column are set to zero as default so I wanted help creating some kind of formula that would do the equivalent of "if value is >0 then copy entire 'row' or 'line' to another place on another sheet and so on so each time someone wants an amount of something it will paste the order line elsewhere but it then gets tricky as the next product ordered, the formula will need to know to paste to the next available line below its predecessor

Is this possible or am I wanting the moon on a stick!!

Many thanks in advance

Rob

[Andy Pope]

Do you need code or to move the records.

Can you make use of autofilter to hide all unwanted rows before printing?

[robski]

not really to move the records, just to copy the rows that contain a specific cell that has a value of 1 or more to an alternative location.

Auto filter will do the job in a roundabout way thanks for that (as to be honest I forgot about it!!) but its basically a sales order tool, I want to send it to my customers for them to select what products they want.

Problem is that 1 sheet is 4 pages long, 1 is 8 pages and if they only have a few items its a waste of paper printing out the order and its a bit much to expect my customers to use auto filter.

Although they can send it back to me and I can do that, they will still probably print it for their records, when what I would like to happen if possible is that when an item is selected (i.e value 1 upwards to 500 max) then the sheet will recognise this and copy the contents of the row (i.e product code, description, price etc) to another location ideally another sheet in the book, so after the order is complete, a summary is available to print from another sheet in the book = least amount of paper/space used.

Its a big ask I know but wondering if anyone has any ideas?

cheers

[Andy Pope]

Rather than do it item by item add a button to process all records at once.
This approach will also get around the problem of them changing their mind and setting the value back to zero, which would mean locating the item and deleting it.

So you can use the auto filter approach. Record a macro of you doing the auto filter, copying the visible cells and then pasting in a new sheet or existing print-sheet.

[robski]

You my son, are an effing genius, exactly what i needed!!

I have never used macro's before, and boy, how cool are they! I can now receive an order and flick a button and it will convert the order into a compact document ready for printing,

Many many thanks