Create Monthly Report In New Workbook

[wezdavies]

Hi,

At the moment we have a separate workbook for each job that is going through that tracks invoices out against invoice in to show profit on each job. What would be the best way to create a monthly report in a separate workbook using data from each job workbook. I would like to get the total amount we have invoiced customers, the total amount we have been invoiced from suppliers and profit to populate a table in a separate workbook along with date and job number to show profit and loss on each job by month? Could i use a VBA code to store data in month order in a new workbook by the click of a button?

Any help would be appreciated.

Thanks,

 

[My Aswer Is This]

Having a separate workbook for every job is not a good plan.
So if you have 30 jobs a days and 30 days in a month and twelve month's in a year now you have nearly 11,000 workbooks every year

Even if you only have a separate workbook for each day you would end up with 365 workbooks per year.

At the most I might have a separate sheet for each order

The more you separate all your data out the harder it is to come up with monthly and yearly totals.

I see this all the time on this forum.

When your boss says how many apples did we sell this year and if you have a separate sheet for each days sales that can even be a little work. If you had all your sales in one sheet the proper formula could tell you in a second how many apples you sold today this week or this year.

You have a field for date a field for item # and on and on a formula then can see how many of item #34 you sold today this week this year.