"Each time I use my 'invoice' template, I'd like to generate a new sequential invoice number, not skipping any numbers and not using any external workbooks to reference for numbers. Can that be done?"
- Operates off of normal Excel template files
- The invoice number increment each time an invoice is saved for the first time
- Numbers should never increment on a saved invoice ever again, nor affect the numbers of any other invoices based on the same template
- It should be automatic
- You will have to turn off macros each time before you save your original invoice template to ready it for use, else the macro we are putting in would cause an error. This macro is only designed to run on workbooks created FROM the template.
- Printing is not allowed until the invoice is saved at least once. (A separate macro to prevent that...)
METHODOLOGY: When the new workbook is saved for the first time it will
- Check and see if a permanent invoice number has been assigned by checking cell AA1.
- If not numbered yet, it will secretly REOPEN the original template file and increment the number in the template itself by one, and save the template again
- It will remember the new number and put that number into the new workbook permanently
- ...marking cell AA1 as "incremented" thus turning off these automacros in the future.
- Continue with a normal SaveAs from that point on.
This macro goes into the ThisWorkbook
module of your new Invoice.xlt
template file. You will need to know the full path to where this template is stored because the template has to be reopened during the SaveAs function.
Nothing says "thanks" like a steak dinner!