Advanced Excel Functions

[rjf3123]

Not real sure how to describe this (I have a sample worksheet I can email)

Excel worksheet, 2 tabs. 1 is an order entry form:

r1 Order Category 1
r2 qty Description Price Extended
r3 1 item 1 $5.00 $5.00
r4 2 item 2 $2.00 $4.00
r5 0 item 3 $7.00
r6 0 item 4 $9.00
r7 1 item 5 $4.00 $4.00
r8
r9 Order Category 2
r10 qty Description Price Extended
r11 1 item 6 $10.00 $10.00
r12 0 item 7 $6.50
r13 0 item 8 $9.95
r14
r15 Order Category 3
r16 qty Description Price Extended
r17 0 item 9 $3.00
r18 0 item 10 $4.00
.
.
.
Tab 2 is the invoice / order form

What I would like to do is copy the rows from tab 1 to tab 2 but only for those rows where the extended amount is > 0. In other words, in the above example only use r1,r2, r3,r4,r7,r9,r10 & r11 and not use r5,r6,r12,r13 and not use r15 to r18 at all because there is no extended prices for the items in Category 3.

While I am at it (and not to be greedy about asking), I would also like to copy the cells the way they are formatted over to tab 2. The height of the row, the shading color of the cell, the text color, etc.

Any thoughts?

Thanks so much in advance.

R. Fisher

 

[Zathras]

This could be done with a bit of fancy macro code, but if you want to keep it simple, you could set autofilter on your columns (A thru D in your example), then set the selection for column A as "custom" with "does not equal" as the relationship and 0 as the value.

The copy and paste. This would pick up the headings for category 3 (in this example) so it is not complete, but it gets you close without any programming. And it will copy formatting if you want.

Select columns A thru D and then from the menu, choose Data/Filter/Auto Filter.

If you want to automate it, use the macro recorder.

 

[rjf3123]

Thanks for the reply. Thinking I am going to have to do some programming to get this to work right (I am not going to be the end user here).

Any tips on the programming side of this? I am sure it can be done but I am very green when it comes to VBA.

Thanks

 

[Gavona]

The above solution does not copy column widths. If this is important then Copy the sheet. Then use filters to display the rows you do not want (Maybe greater than zero would do it). Select them all and Edit DeleteRow.

Once you have sussed doing it manually then do it with the macro recorder on. That will generate the code for you. Have a look at the code, refine it and post it to the VBA forum any questions. They are great here and will be sure to help.


Gavin