Forum Discussion

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

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 ...
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.

mander1281's avatar
mander1281
Copper Contributor
Jan 16, 2024

HansVogelaar  This worked when I did the one where it changed every time we open the workbook but then I saw your code for printing copies in sequential order so I tried it but it isn't working for me,  the printing option to print multiple copies of the invoice with sequential numbers is not coming up for me... what am I doing wrong?

  • mander1281's avatar
    mander1281
    Copper Contributor
    Jan 16, 2024
    Honestly I am not sure! When I did the code you posted Aug 12 2022 06:44 AM it worked perfectly.
    Then I was told they want to print like 50 copies at one and have the invoice number change in sequence as it prints so I tried your next code you posted Aug 12 2022 12:24 PM saved as enable macros file and when I go back in to print a few to test it it doesn't prompt me for the amount of invoices to print. And obviously if I print anyway it doesn't work, just prints whatever if on the current page. Any ideas?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jan 16, 2024

      mander1281 

      If you run the macro

      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
    Next i
End Sub

      you should see a prompt:

      The code will then increment the number in cell D1 on Sheet1 and print that sheet.

      If you want to use another sheet or another cell, adjust the code accordingly.

Resources