How to create invoices in excel VBA?

Please see the following code for creating invoices with VBA.

How my excel workbook looks like

In order to generate the invoices for my different suppliers I have set up two sheets for this. First one being my supplier list and second one being my invoices template which showcases how my invoices will look like. Please click on the images below to view my worksheets.

---

Code

Sub GenerateMyInvoices()

Application.ScreenUpdating = False 'Turns off screenupdating
Supplier_Sheet.Activate
For Each Cell In Range("A3:A10") 'my range that I will look in
    If Cell <> "" Then
 
 
'******************************************** Moving data to the invoice template *******************************
        'Moving data to the invoice template sheet, and saving in excel and pdf formats.
 
        'Moving Supplier Name
        Invoice_Sheet.Range("B12") = Cell
        Invoice_Sheet.Range("D12") = Cell
 
        'Alternatively we can write the following to move the supplier name for example.
        'Invoice_Sheet.Range("B12,D12") = Cell
 
        'The benefit of this is that we are only doing one write action, thus speeding up the script.
        'Also, this is practical as we only have to change one line of code instead of two, should something change.
 
        'Moving Contact Name
        Invoice_Sheet.Range("B13,D13") = Cell.Offset(0, 1)
 
        'Moving Address
        Invoice_Sheet.Range("B14,D14") = Cell.Offset(0, 2)
 
        'Moving amount
        Invoice_Sheet.Range("F18") = Cell.Offset(0, 3)
 
 
        'Now we have moved all the necessary data in order to save each invoice.
 
        'Where we want to save it. please complete this with your own folderpath.
        Dim strfolderpath As String
        strfolderpath = Range("H2")
 
        'Our filename
        Dim FileName As String
        FileName = Cell
 
        'Complete filepath and name
        Dim strPath As String
        strPath = strfolderpath & "\" & FileName
 
        'If we had a specification sheet for terms and conditions, _
                or just multiple invoice sheets that are located in separate worksheets _
                we could use the following syntax to activate the sheets and get a 2-paged invoice.
 
 
        'Please note that it could be good to amend the print layout in the Invoice template so that all your data _
                is included and that you get it in a nice format. You can amend the print area under tab _
                "View/Page Break Preview".
 
 
'******************************************** Saves 1 page in either .xlsx or pdf*******************************
        'saves as .pdf format.
        Invoice_Sheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
        'saves as .xlsx format
        Invoice_Sheet.Copy

        With ActiveWorkbook
             .SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
             .Close SaveChanges:=False
        End With


'******************************************** Saves multiple pages in either .xlsx or pdf***********************
 
        'saves as .pdf
        Sheets(Array(Supplier_Sheet.Name, Invoice_Sheet.Name)).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
        'saves as .xlsx format
        Sheets(Array(Supplier_Sheet.Name, Invoice_Sheet.Name)).Copy

        With ActiveWorkbook
             .SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
             .Close SaveChanges:=False
        End With
 
'******************************************** End of saving options*********************************************


    End If
Next Cell


Application.ScreenUpdating = True 'Turns on screenupdating again
​
MsgBox ("Done")


End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

Invoice generation

.zip

Download ZIP • 44KB