Need help to macro my invoice number [Solved/Closed]

Janit - Feb 18, 2015 at 11:59 PM - Latest reply: ac3mark 9964 Posts Monday June 3, 2013Registration dateModeratorStatus July 19, 2018 Last seen
- Aug 12, 2016 at 07:53 PM
Hello, I wish to increment my invoice number everytime I open my invoice....format is 1-2015-001 and increment by one 1-2015-002 should be the next number, but do to the format I can't get it to work. I also want to be able to clear data fields and save using the invoice number and when I open I would like the new number to appear...eg. I made 1-2015-001 saved it automatically and upon reopening would like the next number to appear 1-2015-002. How can I make this happen? Thank you in advance.



See more 

6 replies

Best answer
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 19, 2015 at 05:23 AM
1
Thank you
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.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1671 users this month

it works great, I can now increment my invoice numbers no problem....but I would like to save as an .xls file and this is giving me issues.....i tried to alter the code as follows but it's not working, what am I doing wrong?

'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.
Best answer
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 19, 2015 at 06:40 PM
1
Thank you
Hello Janit,

Change the previous codes to this:-

Sub NextInvoice()

    Range("J6").Value = Range("J6").Value + 1
    Range("B6:H9").ClearContents
    Range("A11:H48").ClearContents
    Range("I11:J48").ClearContents
    
End Sub
 Sub SaveInvoiceAsxlsx()
 Dim NewFN As Variant
     ActiveSheet.Copy
     NewFN = "C:\Users\aaa\" & Range("B6").Value & ".xlsx"
     ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
     ActiveWorkbook.Close
     
     NextInvoice
     
 End Sub


which should save your invoice as an xlsx document.

Again change the "aaa" to your filepath.

Just assign this macro [SaveInvoiceAsxlsx()] to the button as this one calls the one above it also. One button is all you need.

The ranges in the above codes are based on my mock-up invoice so remember to change them to suit your actual invoice.

Let us know how it goes.

Cheerio,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1671 users this month

0
Thank you
i will definitely give this a try and i'll let you know!
moosze 1 Posts Friday August 12, 2016Registration date August 12, 2016 Last seen - Aug 12, 2016 at 07:44 PM
0
Thank you
Hi there,

Is that possible if my invoice number mix with character? Let say INV0001 . Hows that the code will be?

Thanks
ac3mark 9964 Posts Monday June 3, 2013Registration dateModeratorStatus July 19, 2018 Last seen - Aug 12, 2016 at 07:53 PM
Please start you own thread so that we can answer your specific question. Reference this post, please.