Expert guide

Bulk Create PDF Invoices from Excel with VBA

One template, one list, one click: a named PDF for every invoice

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: keep invoice headers and line items in two Excel Tables, design an Invoice sheet whose fields are formulas driven by one input cell (the invoice number), and list the lines with FILTER. A VBA loop sets the input cell to each invoice number, recalculates, and exports the sheet with ExportAsFixedFormat to a PDF named like INV-2026-0142 - Customer.pdf, optionally emailing it. Our Excel document generator is built on this pattern.

Many small businesses bill from a spreadsheet: the hours or deliveries are already listed in Excel, and someone copies each customer's figures into an invoice template, saves a PDF and moves on to the next. At 40 invoices a month that is a lost day. A template driven by formulas and a short macro produces all 40 in under a minute, consistently formatted, with no copy-paste errors.

Step 1: The Data Tables

TableColumns
tblInvoicesInvoiceNo, InvoiceDate, DueDate, CustomerID, PONumber, Status (Draft/Ready/Issued)
tblLinesInvoiceNo, Description, Qty, UnitPrice, TaxRate
tblCustomersCustomerID, Name, Address lines, Email, PaymentTerms

Step 2: The Invoice Template

On a sheet named Invoice, put the current invoice number in one cell, say H2. Everything else looks it up.

Invoice date:   =XLOOKUP($H$2, tblInvoices[InvoiceNo], tblInvoices[InvoiceDate])
Customer ID:    =XLOOKUP($H$2, tblInvoices[InvoiceNo], tblInvoices[CustomerID])
Customer name:  =XLOOKUP(H5, tblCustomers[CustomerID], tblCustomers[Name])
Address:        =XLOOKUP(H5, tblCustomers[CustomerID], tblCustomers[[Address1]:[Postcode]])

Line items (spills down from B12):
=FILTER(tblLines[[Description]:[UnitPrice]], tblLines[InvoiceNo] = $H$2, "")

Line totals (E12, spills alongside):
=IF(B12# = "", "", CHOOSECOLS(B12#, 2) * CHOOSECOLS(B12#, 3))

Subtotal:  =SUM(E12#)
Tax:       =SUMPRODUCT((tblLines[InvoiceNo] = $H$2) * tblLines[Qty] * tblLines[UnitPrice] * tblLines[TaxRate])
Total:     =Subtotal + Tax

Set the print area to the invoice layout, fit to one page wide, and add your logo and payment details as static content. Change H2 by hand to check a few invoices look right before automating.

Step 3: The Export Loop

Option Explicit

Sub ExportInvoices()
    Dim wsInv As Worksheet, lo As ListObject, r As ListRow
    Dim folder As String, fileName As String, n As Long

    Set wsInv = ThisWorkbook.Worksheets("Invoice")
    Set lo = ThisWorkbook.Worksheets("Invoices").ListObjects("tblInvoices")
    folder = ThisWorkbook.Path & "\Invoices " & Format(Date, "yyyy-mm") & "\"
    If Dir(folder, vbDirectory) = "" Then MkDir folder

    Application.ScreenUpdating = False
    For Each r In lo.ListRows
        If r.Range(1, lo.ListColumns("Status").Index).Value = "Ready" Then
            wsInv.Range("H2").Value = r.Range(1, lo.ListColumns("InvoiceNo").Index).Value
            Application.Calculate
            fileName = folder & wsInv.Range("H2").Value & " - " & _
                       CleanName(wsInv.Range("B5").Value) & ".pdf"
            wsInv.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileName, _
                                      Quality:=xlQualityStandard, OpenAfterPublish:=False
            r.Range(1, lo.ListColumns("Status").Index).Value = "Issued"
            n = n + 1
        End If
    Next r
    Application.ScreenUpdating = True
    MsgBox n & " invoices saved to " & folder, vbInformation
End Sub

Function CleanName(ByVal s As String) As String
    Dim ch As Variant
    For Each ch In Array("\", "/", ":", "*", "?", """", "<", ">", "|")
        s = Replace(s, ch, "-")
    Next ch
    CleanName = Left$(Trim$(s), 80)
End Function

Only rows marked Ready are exported, and each is marked Issued afterwards, so running the macro twice never duplicates invoices.

Step 4: Invoice Numbers That Never Change

  • Assign the number when the invoice moves from Draft to Ready, with a macro that writes the next number as a value.

  • Store the last used number in a Settings cell; increment it in the macro, never with a formula.

  • Never reuse or delete issued numbers; cancel with a credit note instead.

Step 5: Email Each Invoice (Optional)

Inside the loop, after exporting, create an Outlook draft to the customer's email with the PDF attached. The full pattern, with HTML body and logging, is in emailing Excel reports with Outlook VBA and personalized emails from Excel.

Beyond Invoices

DocumentData sourceTypical variation
Customer statementsOpen invoices per customerFILTER by customer and status
CertificatesCourse or test resultsOne page per person
PayslipsPayroll calculation tableOne page per employee, password-protected delivery
QuotesQuote header and linesValidity date and terms
Delivery notesOrder linesSignature area

When payment tracking, reminders and statements matter as much as producing the PDF, a database is safer than a workbook; see our Access invoice and payment tracker, or for Access-based PDFs, exporting Access reports to PDF.

FAQ

Bulk PDF invoices FAQ

Templates, line items, numbering and email

Design one invoice template sheet whose cells pull data with formulas based on an invoice number in a single input cell. A VBA macro loops through the list of invoice numbers, puts each number in the input cell, recalculates, and exports the template sheet to a PDF named after the invoice.

Keep line items in a separate table with the invoice number on each row, and use FILTER (Microsoft 365) on the template to list the lines for the current invoice. Leave enough rows in the layout for the maximum number of lines, or use a second page for long invoices.

Yes, but generate numbers once and store them as values, for example with a macro that assigns the next number when an invoice is finalised. Formula-based numbers can change if rows are sorted or deleted, which is unacceptable for issued invoices.

Yes. After exporting each PDF, the macro can create an Outlook email to the customer's address with the PDF attached, as a draft for review or sent directly. Classic Outlook is required for VBA automation.

When you need payment tracking, reminders, tax reporting, customer statements and an audit trail of changes, accounting software or a database is safer. Excel works well for generating documents from data that already lives elsewhere.