Copy Cells into Blank Invoice Based on Row and Column Criteria

[alton]

Firstly, what a great find this forum is - thank you in advance to all that will help! It seems I've bitten off more than I can chew with this project!

Our company is invoiced by third party suppliers, and we then have to charge these costs on to various companies within the group of companies. All costs that come in are plotted on an Excel sheet, a separate row for each cost (supplier, invoice number, description etc.) with the amount of the invoice plotted in a corresponding column based on which company we intend to charge these costs onto.

Monthly we manually prepare invoices for each of the companies we charge these costs onto. This invoices a lot of filtering, copying, pasting and formatting. We intend to use Excel to automate this process.

You can refer to the attached spreadsheet for an example of the reconciliation sheet that holds all the costs, and a sample invoice.

http://www.lagrandemoda.co.uk/Test.xls

As you can see, the costs are added to the reconciliation sheet in date order. When they are copied over to the invoice, we filter by whatever company we're invoicing (e.g. Company 123, Company ABC.) and then filter by supplier to pick up each cost in alpabetical order. This way, the invoice is prepared in alphabetical order, with each supplier as a sub-heading on the invoice, followed by all costs incurred by this supplier. If a row has a "H" or a "D" in the hold column, this invoice cannot be charged on and should not be copied across to the invoice.

This sequence is then repeated for each company in each column.

Can this be automated?

Your hints, tips, discussion, codes etc. would be much appreciated.

Alton

[JBeaucaire]

I would

1) use the AUTOFILTER to filter the hold column for blanks only (hides all "H" and "D" rows)
2) filter the main company column by non-blanks (show only rows with $ amounts in them)
3) copy the remaining visible rows to a temp sheet
4) rearrange the data so it's in the right order with the vendor on the left
5) sort the temp data by the vendor to put all the same vendor items together
6) parse through the temp data one row at a time and copy it into your template in groups the way you've shown in your sample
7) save the template (optional)
8) clear the temp sheet
9) repeat above with next company column

[alton]

Thanks Jerry.

Would this be easy to "record" via a Macro? Or would I need to use some VBA? I can use VB through Access but I haven't had much experience with Excel. Any code or tips I can use?

Alton

[JBeaucaire]

Crossposted: http://www.mrexcel.com/forum/showthread.php?t=505256

Alton, be sure to take a moment to read through the Forum Rules so you can use and follow them effectively.

For instance, crossposting requires you to include links to your question on the other forums. The last thing you want to do is waste people's time, right? If your question were being actively worked on in one forum and possibly even solved, people in the other forum(s) would literally be wasting their precious donated free time working on yours, right?

Cross posting isn't illegal, and I know it makes sense to you, but you must follow this rule.

============

Your issue isn't a small one. It's definitely a macro, that's what the macro recorder does, provide you some base code to work from after you do it once a few times.

I offered an outline above of the steps you could use in building your macro. As you resolve each step you'll learn more and more about Excel VBA, which is your goal, right?

You can use the recorder to work out small steps, then tweak the resulting code so that it is more dynamic. Post your code samples here for tips and tweak suggestions as you work through it. As per forum rules, be sure to post code in between code tags, like mentioned in my signature below.