MrExcel.com - Your One Stop for Excel Tips & Solutions

Next Invoice Number

July 16, 2017 - by

Next Invoice Number

Microsoft Excel offers many invoice templates that you can download. But there is no built-in way to increment to the next invoice number.

I recorded this video showing how to add a few lines of VBA code to your workbook so you can save each invoice as a new file. The macro then clears the invoice and adds 1 to the invoice number.

With 166K views and hundreds of comments, I am finding the same questions are coming up again and again. It has become impractical to ask people to read through 800 comments because the answer to their question has been posted six times before. So - for popular questions, I am posting the code here.

FAQ #1

Can you type the code for me because I am unable to type?

Sub NextInvoice()
    Range("E5").Value = Range("E5").Value + 1
    Range("A20:E39").ClearContents
End Sub

Sub SaveInvoiceWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a New Workbook
    ActiveSheet.Copy
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

FAQ #2

I want to Save the Invoice as a PDF on a Windows PC

Note

This code only works in Windows versions of Excel 2010 or newer. There is different code for a Mac.

You have to select the range that contains the invoice and do Page Layout, Print Area, Set Print Area. If you skip this step, then the buttons used to run the macro will appear in your invoice! 

Sub SaveInvoiceAsPDFAndClear()
    Dim NewFN As Variant
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    Range("E5").Value = Range("E5").Value + 1
    Range("A20:E39").ClearContents
End Sub

FAQ #3

I want to Save the Invoice as both an Excel file and a PDF in a different folder

Sub SaveInvoiceBothWaysAndClear()
    Dim NewFN As Variant
    ' Create the PDF First
    NewFN = "C:\aaa\PDFInvoices\Inv" & Range("E5").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    ' Next, Save the Excel File
    ActiveSheet.Copy
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    ' Increment the invoice number
    Range("E5").Value = Range("E5").Value + 1
    ' Clear out the invoice fields
    Range("A20:E39").ClearContents
End Sub

FAQ #4

My invoice number has numbers and letters

You will have to adapt the code. Here are some examples. Heidi has an invoice number like SS15001. When I look at that invoice number, it is a two-letter prefix followed by 5 digits. The new invoice number has to use the LEFT(,2) and MID(,3,5): 

Range("E5").Value = Left(Range("E5").Value, 2) & 1 + Mid(Range("E5").Value, 3, 5)

Here is a more complex example. The invoice number is IN-1234-HA where the IN- stands for Invoice. The 1234 is a sequential number. The HA is the first to letters of the customer name found in B10. 

LeftPart = Left(Range("E5").Value, 3)
MidPart = Left(Range("E5").Value, 4, 4) + 1
EndPart = Left(Range("A10").Value, 2)
Range("E5").Value = LeftPart & MidPart & EndPart

You could simplify the above four lines as follows: 

Range("E5").Value = Left(Range("E5").Value, 3) & Left(Range("E5").Value, 4, 4) + 1 & Left(Range("A10").Value, 2)

FAQ #5

I have other macros in the workbook (such as SpellNumber) and need the macros to be saved as well.

In order to allow your other macros to run in order to have the Numbers-to-Words function to keep working, the strategy is a bit different. Instead of copying just the invoice sheet to a new workbook and using SaveAs, you will (a) Remember the path and file name of the workbook, (b) Use SaveAs to save the entire workbook with the Invoice number in the name, (c) Delete the original workbook. (d) Use SaveAs to save the workbook with the original name. 

Sub SaveInvoiceWithNewName()
    Dim OrigFN as Variant
    Dim NewFN As Variant
	' Remember the original path and file name
	OrigFN = ThisWorkbook.FullName
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
	' Save a copy with the new name
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
	' Delete the original workbook so you can save without warning
	On Error Resume Next
	Kill (OrigFN)
	On Error Goto 0
	' Save again as the original file name
	ActiveWorkbook.SaveAs OrigFN, FileFormat:=xlOpenXMLWorkbook
    NextInvoice
End Sub

FAQ #6

I need to protect the worksheet so my employees can only change some cells. When I run your macro, I get "The cell you are trying to change is protected and therefore read-only"

You can unprotect the sheet in the macro, write the new invoice number, and then protect the sheet. This only has to be done in the NextInvoice macro. The other macro is not making changes to any cells. Here is the code if you are using protection without a password: 

Sub NextInvoice()
    ActiveSheet.Unprotect
    Range("E5").Value = Range("E5").Value + 1
    Range("A20:E39").ClearContents
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Here is the code if you have protected the sheet with a password of Tomato. Note that the password appears in this code twice. Change the password to the real password in two places.
Sub NextInvoice()
    ActiveSheet.Unprotect Password:="Tomato"
    Range("E5").Value = Range("E5").Value + 1
    Range("A20:E39").ClearContents
    ActiveSheet.Protect Password:="Tomato", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

FAQ #7

I don't want to have the Save button in the Saved workbook

This is tricky because the name of your shape will change when the worksheet moves to a new workbook. Very carefully follow these steps:

  1. Open the invoice macro workbook
  2. Right-click on the sheet tab that contains your invoice. Choose Move or Copy.
  3. In the To Book: dropdown, choose New Book. Choose the checkbox for Create a Copy. Click OK. This step 3 simulates the ActiveSheet.Copy in the VBA.
  4. Now that you have the invoice worksheet in a new workbook, Ctrl-click on the shape that you want to delete. Ctrl+Click will select the shape without running the macro.
  5. With the shape selected, look to the left of the Formula Bar. The Name Box will show a name such as "Rounded Rectangle 1". Very carefully build a new line of code to delete this shape as shown below just after ActiveSheet.Copy:
Sub SaveInvoiceWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a New Workbook
    ActiveSheet.Copy
    ActiveSheet.Shapes("Rounded Rectangle 1").Delete
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

FAQ #8

When I open any previously saved workbook, it is still pulling numbers from the latest invoice.

This can happen if you have formulas on your Invoice worksheet that are pointing to other worksheets in the workbook. When the code copies just the Invoice to a new workbook, it is setting up external links to the original workbook. Change the first macro to this: 

Sub SaveInvoiceWithNewName()
    Dim NewFN As Variant
    ' Save a Copy of this workbook before clearing
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveCopyAs Filename:=NewFN
    ' Clear this workbook for new invoice
    NextInvoice
End Sub

Troubleshooting & Error Messages

  1. The following features cannot be saved in macro-free workbooks: VB Project. Answer: Your file is currently saved as an XLSX file. I am equally annoyed that Microsoft chose to use a broken file type as the default. Use File, SaveAs and change the file type to either XLSB or XLSM.
  2. Type Mismatch. Answer: The code expects your invoice number to be a number. If you have SS15001 as an invoice number, you will have to figure out how to adapt your code. See FAQ #4 above.
  3. Compile error Expected line number or label or statement or end of statement on NewFN = “F:\Robin\business\PCreceipts\Inv” & Range(“H10”).Value & “.xlsx”. Answer: VBA does not like slanted quotation marks (also called Typographers quotes). Type the quotation mark in VBA and you will get "F:\Robin\business\PCreceipts\Inv" & Range("H10").Value & ".xlsx"
  4. We couldn't find C:\User\Jelen\Documents" Answer: The file path has to be exact. Are you sure you didn't mean C:\Users\Jelen\Documents? (Note the "s" at the end of users)
  5. Run Time Error 1004. Document Not Saved. Answer: The file path has to be exact. Just before Saving the file add a new line with MsgBox NewFN. Run the code. A box will pop up showing the file path and file name. Make sure there is a path separator between the path and file name.
  6. Run Time Error '1004'. Method 'SaveAs' of object '_Workbook' failed. Answer: The FileFormat has to be FileFormat:=xlOpenXMLWorkbook. When you read it, it should sound like "Excel Open XML Workbook". It is not Ex One Open XML Workbook. Yes, it is confusing that a number 1 and a lower case L look alike in the video. 1l. Change your X1OPENXMLWORKBOOK to XLOPENXMLWORKBOOK.