Forum Discussion

AJ-FLAME's avatar
AJ-FLAME
Copper Contributor
Aug 12, 2022

Automatically inserting a sequential invoice number

Hello everyone,

Currently I pay a company to print blank invoices with sequential numbers.

I am attempting to find a way to print a similar blank invoice (that I have created in excel) but on each copy i would like the invoice number to increase by one.  Is this possible to do without manually changing everyone? 

I do not totally understand the concept of Macro's, but I have a basic knowledge on how to attach one to my system if necessary 

excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Aug 12, 2022

    AJ-FLAME 

    The code that I posted will only run when you open the workbook.

    As an alternative, remove the Workbook_Open code.

    Then select Insert > Module in the Visual Basic Editor.

    Copy the following code into the module:

    Sub PrintInvoices()
    Dim i As Long
    Dim n As Long
    n = Val(InputBox("How many copies do you want to print?"))
    For i = 1 To n
        With Worksheets("Sheet1").Range("D1")
            .Value = .Value + 1
        End With
        Worksheets("Sheet1").PrintOut
    End With
End Sub

    Running this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.

    If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 12, 2022

    AJ-FLAME 

    Let's say you want the invoice number in cell D1 on Sheet1.

     

    Press Alt + F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook under Microsoft Excel Objects in the Prpject Explorer pane on the left.

    Copy the following code into ThisWorkbook:

    Private Sub Workbook_Open()
    With Worksheets("Sheet1").Range("D1")
        .Value = .Value + 1
    End With
End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (*.xlsm).

    You'll have to allow macros when you open it.

     

    Each time you open the workbook, the invoice number will be increased by 1.

    • AJ-FLAME's avatar
      AJ-FLAME
      Copper Contributor
      Aug 12, 2022
      if I print multiple copies will each copy have a new number? or will the number only change when i reopen the file?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 12, 2022

        AJ-FLAME 

        The code that I posted will only run when you open the workbook.

        As an alternative, remove the Workbook_Open code.

        Then select Insert > Module in the Visual Basic Editor.

        Copy the following code into the module:

        Sub PrintInvoices()
    Dim i As Long
    Dim n As Long
    n = Val(InputBox("How many copies do you want to print?"))
    For i = 1 To n
        With Worksheets("Sheet1").Range("D1")
            .Value = .Value + 1
        End With
        Worksheets("Sheet1").PrintOut
    End With
End Sub

        Running this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.

        If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.

    • Pindiboy's avatar
      Pindiboy
      Copper Contributor
      Jun 22, 2024

      HansVogelaar hi. What if i also want to protect the cell so manually changing the invoice number will not be allowed. the code below works fine but if i protect the cell it gives an error

  • lilygracia's avatar
    lilygracia
    Copper Contributor
    Mar 21, 2024
    Hello,

    Yes, it's absolutely possible to achieve what you're looking for without manually changing the invoice numbers on each copy. Using invoicing software like FreshBooks, Xero, or Invoicera can simplify this process for you.

    These platforms offer features for creating and managing invoices, including automatic numbering. Here's how you can do it:

    1. Create Your Invoice Template: Design your invoice template in FreshBooks, Xero, or Invoicera. Include all necessary details such as your company information, customer details, itemized list of products or services, amounts, taxes, and payment terms.

    2. Set up Auto-Numbering: In the settings or preferences section of the software, look for an option related to invoice numbering or sequencing. You can usually specify a starting number for your invoices. Once set, the system will automatically increment the invoice number with each new invoice you create.

    3. Generate Invoices: Whenever you need to create a new invoice, simply use the "Create Invoice" feature in the software. The system will automatically assign the next sequential invoice number based on your predefined settings.

    4. Print Blank Invoices: After generating the invoices in the software, you can choose to print them directly from the platform. Each printed invoice will have a unique, sequentially increasing invoice number, eliminating the need for manual adjustment.

    5. Optional: Learn About Macros: While using invoicing software should cover your needs, if you're interested in learning more about macros in Excel for future reference, there are resources available online to help you understand and implement them. Macros can automate repetitive tasks in Excel, but for your current requirement, using dedicated invoicing software would be more efficient.

    By utilizing invoicing software with auto-numbering capabilities, you can streamline your invoicing process and eliminate the need for manually changing invoice numbers on each copy. Let me know if you need further assistance with any of the software options mentioned.

Resources