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

Sorry about that, it was air code.

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
pauljjk's avatar
pauljjk
Copper Contributor
Sep 13, 2023

Is there a way to create a save as button that will save the filename as the invoice number? HansVogelaar 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 13, 2023

    pauljjk 

    Using a variation of the code in my previous reply (invoice number in D1 on Sheet1), you could run the following macro from another workbook. Just make sure the invoice workbook is the active workbook when you run the macro.

    Sub SaveInvoices()
    Dim i As Long
    Dim n As Long
    Dim p As String
    n = Val(InputBox("How many copies do you want to print?"))
    p = ActiveWorkbook.Path
    If Right(p, 1) <> Application.PathSeparator Then
        p = p & Application.PathSeparator
    End If
    For i = 1 To n
        With Worksheets("Sheet1").Range("D1")
            .Value = .Value + 1
            ActiveWorkbook.SaveCopyAs p & "Invoice " & .Value & ".xlsx"
        End With
    Next i
End Sub

Resources