Automate creation of monthly invoices

Each month I manually generate client invoices in Excel and I would really like to automatically create these to save time and effort. Right now, the steps I take for each client invoice are to:

* copy and paste the previous month's report and rename the file by updating the 3 character month abbreviation in the filename (eg., change filename Invoice_Aug06_XXX.xls to Invoice_Sep06_XXX.xls)
* open the file
* update filenames used in several cell formulas to point to the previous month's filename (eg., change Invoice_Aug06_XXX.xls to Invoice_Sep06_XXX.xls)
* increment the invoice number in cell J3 by 1 [that involves using a formula to doublecheck, =EXACT(J3,'[Invoice_Aug06_XXX.xls]Sheet1'!$J$3) where J3 contains the text 'Invoice #: XXX-014'. If the formula returns TRUE, then I increment the text to read 'Invoice #: XXX-015']

The rest of the invoice is filled with sumproduct and other functions that are based on today() and that produce the current data required. I then pdf the file to email to the client.

So, I would like to figure out a way to stop repeating the above steps dozens of times a month and to autogenerate the invoicing process through to the pdf step without having to do anything but check the pdf. (I will typically have to go back and tweak something in the spreadsheet and re-pdf, but that is month- and client-specific and doesn't lend itself to automation.)

I haven't a clue as to how to go about it. Can anyone come up with a clever solution?

You need VBA to produce some macros. It is not going to be 100% straight forward, but far from impossible.

I suggest to start with you turn on the macro recorder, then complete the process for 1 customer for 1 month. this will produce raw code that can repeat the exact steps over and over. Trouble is you wont want to do it exactly, because the months change, the invoice numbers change etc. Someone will then have to edit the code to make it more flexible.

Matt

Knowing that I have to devise a macro is helpful. Let's start small: how do you change the name of another file within a macro (eg., change filename Invoice_Aug06_XXX.xls to Invoice_Sep06_XXX.xls)?

Hi,

Two solutions described below :

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

HTH
Carim