Hello Janit,
Basically, this piece of code will increment your invoice number:-
Range("Whatever").Value = Range("Whatever").Value + 1
Obviously, the "Whatever" needs to be replaced with your actual range. It may be "B5" or "C10" or "D6" etc. It depends on where your invoice number is situated in the invoice.
I have done a quick "mock-up" invoice here:-
https://www.dropbox.com/s/00cdpxfuf42fbzn/Test%20Invoice.xlsm?dl=0
for you to have a look at.
The code is as follows:-
Sub NextInvoice()
Range("J6").Value = Range("J6").Value + 1
Range("B6:H9").ClearContents
Range("A11:H48").ClearContents
Range("I11:J48").ClearContents
End Sub
Sub SaveInvoiceAsPDF()
'Copy Invoice to a new file
NewFN = "C:\Users\aaa\" & Range("B6").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
NextInvoice
End Sub
The ranges to be cleared should be obvious in the above code. All the "used" information should be cleared from the invoice.
If you open the visual basic editor, you will see the code relevant to the "mock-up" invoice. The code won't work in the sample as you will need to enter your own fie path (NewFN = "C:\Users\aaa\" & Range("B6").Value & ".pdf"). The "aaa" is the relevant part for you. You will see that for this "mock-up", the invoice number is in "J6".
The code will save the invoice as a PDF file based on the client name ("B6"). For the invoice number format that you want to use, you will have to format the cell (in this case "J6") to "custom". Highlight the cell, right click on it, select "format cells" then select custom. In the "type" box, type in this:-
#-####-###
then click OK. Your selected invoice number format should then work when incrementing. But, before you do this, enter your first invoice number (1-2015-001) in J6 as 12015001.
I have assigned the macro to a button as you can see. I think that this is a better idea than a "WorkBook_Open" event because you may have a number of invoices to do in a day and it could get quite tiresome to close the work book every time you complete an invoice just to save it.
You will need to save the work book as a macro enabled work book (file type xlsm).
Let us know what you think.
Cheerio,
vcoolio.
'Copy Invoice to a new file
NewFN = "C:\Users\Janit\Desktop\" & Range("S1").Value & ".xls"
ActiveSheet.ExportAsFixedFormat Type:=xlTypeXLS, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
I keep getting an error when I try to run this macro.
Do I also need to remove the 'Copy Invoice to a new file ?
And can I add 2 macros to one button or do I need to make 2 buttons to get everything to work?
Hope to hear from you soon.