Macro needed for billing purposes -- any general code ideas ?

[Zarlot]

Here is what needs to be done:

I get a "flat" file (as an excel spreadsheet) twice a month with
billing data for about 50 customers totaling about 10,000 rows.

So, for example, you might see 20 rows of Customer A along with type
of fees and the different amounts in the appropriate fields/columns.
The next month, you might see 30 rows of Customer A.

Anyway, what I have to do with this data is make 50 different
spreadsheets for each customer. So, for Customer A, I would copy and
paste all of Customer A's data from the original master spreadsheet to
a separate spreadsheet. I then must subtotal this by type of fee,
count, etc.

Then I need to save this file in the appropriate folder as Customer A
Invoice Details Date.xls. I then need to do this with the other 49
customers.

I'm not looking to be spoon fed of course, but does anyone know off
the top of their head some good example code to get started on a
project like this? I know this is very simple in the grand scheme of
stands, but if I could get my hands on a good example or have some
general guidance for dealing with automating this, that would be
great.

It seems to me where someone who knows how to be more elegant in their
coding could save some time would be by automating the transferring of
the different customers' data without using, say, a customer list that
the macro references. Really that's what's going through my mind
right now...how to do this without using a list...



Thanks

 

[Ron de Bruin]

Hi Zarlot

Start with this one to create the workbook
http://www.rondebruin.nl/copy5.htm#workbook

Will help you tomorrow with the changes
But first get this working

 

[Zarlot]

Hi Zarlot

Start with this one to create the workbookhttp://www.rondebruin.nl/copy5.htm#workbook

Will help you tomorrow with the changes
But first get this working

Thanks a bunch! This macro with just a little bit of tweaking is
working well preliminarily. I am not finished with it, as I couldn't
spend too much time with it today, but one thing I'm going to have to
"change" to adapt to my situation is...

1) The first column/field is actually called "Count" and has a 1 in
each cell of the field. So, I'm going to have to make sure the macro
is scanning the second column instead of the first, which I don't
think is going to be a problem.

2) Roughly the first 4 rows in my template actually includes a jpeg,
so I need to copy that each time to the new spread sheet. This
shouldn't be a problem I don't think.



Thanks for your help!