Invoicing Challenge - Copy Rows based on Values

[kescco]

Hello All - Any Help Is Appreciated

I need to copy all the rows assigned to "David" in the "CUSTOMER" column, to a template file entitled tempate.xlt. I would like to
insert them starting at A3. I also need to do the same for the remaining names (each as a separate invoice).

YET, just figuring out how to copy the rows based on the specific names would be a wonderful starting place.


<link rel="File-List" href="demo_files/filelist.xml"><style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} x\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style><style id="Book2_32052_Styles"> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .xl1532052 {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl2232052 {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl2332052 {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl2432052 {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:"\[$$-409\]\#\,\#\#0\.00"; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;} .xl2532052 {padding:0px; mso-ignoreadding; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; background:lime; mso-pattern:auto none; white-space:nowrap;} .xl2632052 {padding:0px; mso-ignoreadding; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; background:lime; mso-pattern:auto none; white-space:nowrap;} .xl2732052 {padding:0px; mso-ignoreadding; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:"\[$$-409\]\#\,\#\#0\.00"; text-align:left; vertical-align:bottom; border:.5pt solid windowtext; background:lime; mso-pattern:auto none; white-space:nowrap;} --> </style>

<table style="width: 339pt; border-collapse: collapse; table-layout: fixed;" x:str="" border="0" cellpadding="0" cellspacing="0" width="451"><tbody> <tr style="height: 12.75pt;" height="17"> <td style="width: 121pt; height: 12.75pt;" class="xl2532052" height="17" width="161">Item Description</td> <td style="border-left: medium none; width: 61pt;" class="xl2532052" width="81">Item</td> <td style="border-left: medium none; width: 21pt;" class="xl2632052" width="28">Qty</td> <td style="border-left: medium none; width: 48pt;" class="xl2732052" width="64">Price</td> <td style="border-left: medium none; width: 40pt;" class="xl2732052" width="53">Total</td> <td style="border-left: medium none; width: 48pt;" class="xl2732052" width="64">Customer</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Speekers</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">111222</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">1</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="14.99">$14.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="14.99" x:fmla="=C2*D2">$14.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">David</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Flowers</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">222333</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">1</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="177">$177.00</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="177" x:fmla="=C3*D3">$177.00</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">Joanne</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Colas</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">333444</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">3</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="1.99">$1.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="5.97" x:fmla="=C4*D4">$5.97</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">David</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Canned Beans</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">444555</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">3</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="5.99">$5.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="17.97" x:fmla="=C5*D5">$17.97</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">Joanne</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Tennis Shoes</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">555666</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">3</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="7.99">$7.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="23.97" x:fmla="=C6*D6">$23.97</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">Teresa</td></tr> <tr style="height: 12.75pt;" height="17"> <td style="border-top: medium none; height: 12.75pt;" class="xl2232052" height="17">Green Socks</td> <td style="border-top: medium none; border-left: medium none;" class="xl2232052" x:num="">666777</td> <td style="border-top: medium none; border-left: medium none;" class="xl2332052" x:num="">1
</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="19.99">$19.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052" x:num="19.99" x:fmla="=C7*D7">$19.99</td> <td style="border-top: medium none; border-left: medium none;" class="xl2432052">Teresa</td></tr></tbody></table>​

<!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!----------------------------->
I know about AUTOFILTER, but I am hoping for a better option seeing the full spreadsheet will contain multiple names and items.

Can VBA do this?

Thank You,

Kescco

 

[analyst44]

Norie's solution (post #6) @ OzGrid worked really well for me a few weeks ago when I had a similar concern:

http://www.ozgrid.com/forum/showthread.php?t=46361

It takes a bit of tweaking of the code, but if you can read VBA and the logic, it's no sweat. If you need some help, just post back and I or someone else here can help you with it.

 

[kescco]

Analyst44,

Yes, the #6 post is powerful. I can make the column changes and the copying is done. Now, to figure out how to tie the created sheets to the inventory.xlt file to create the invoice. I wonder if something to be done to the aforementioned macro to do this?

Kescco

 

[analyst44]

I have actually done a generic version of this just recently. It allows the user to pick any column from A to R (using a simple userform with option boxes) for this "Filter and Save" mechanism and then it uses the names of the filtered values to name the file and save it to the directory where the original is stored. Would that help?

I do not have it in front of me, but I can hollow it out and add some dummy data and post it later if they allow attachments. If not, we can work through PM and go from there...

 

[kescco]

Yes, I would like that.

Kescco

 

[analyst44]

PM me your email address. In the filters at the bottom of my "reply" page it says "You may not post attachments."

I'll just email to you...

 

[analyst44]

I never received a PM.... Good luck if you decide to go your own route.