Automatic PDF Invoice Generator Using Excel VBA – Free Download

Data Analysis (English)

In this article, I will share an idea to help reduce work time for employees and business owners by using excel VBA to automatically generate PDF invoices. This method can also be adapted for other forms like quotations or tax invoices.

1.Why use Excel VBA to generate PDF invoices automatically ?

1.1) Boost Efficiency

Imagine this: in one day, 10 customers purchase products from your company, and 3 of them are returning customers. wouldn’t it be better if you already had those returning customers saved in your database and only needed to enter the new ones? Good news – I’ve built a simple customer database system into the template. It automatically pulls customer info into the invoice sheet using Excel formulas.

1.2) Save Time

I’ve already written the VBA code in the template. With just one click, it can automatically generate multiple  invoices at once. You can even use that time to focus on other tasks while it runs.

1.3) Reduce Errors

Since all the excel formulas are already set up, you only need to fill out the data in the database sheet. That’s all it takes to generate accurate in voice PDFs –  no need to copy or type anything manually.

1.4) For Small Business Owners who want to keep costs low

Some companies spend thousands on paid invoicing software. But in this article, I’m giving away the template for free. (If it had a price tag, it’d be worth a few hundred.)

2. Explanation of each sheet in the excel VBA invoice generator

The file is divided into 4 main sheets: Invoice, Order, Product, Customer

2.1) Invoice Sheet

  • You can freely edit the form layout, such as the company address, Logo and remarks, as needed.
  • On the right-hand side, I’ve already assigned macros to the following buttons:
    • Run Invoice PDF Individual : Click to save the current invoice as PDF file.
    • Run invoice PDF Batch : Click to generate PDF files for all delivery numbers listed in Column U.
    • Clear : Click to clear the data on the invoice sheet.
  • File Name : The file will be saved as “INV” + “Delivery No.”
  • Save Path : Paste the folder path where you want the PDF files to be saved.
  • Column for Batch Run : Paste all the delivery numbers you want to generate. When clicking Run invoice PDF Batch, the system will generate PDFs for all listed delivery numbers.

2.2) Order Sheet

  • Order Date : The date when the customer places the order.
  • Delivery Note No. : The format follows “Year-Month-Sequence” (e.g., 20250400004). A formula has been applied so that when a new number is entered, a red underline will appear to improve readability. This value will also be automatically referenced on the Invoice sheet.
  • Customer ID : Once entered, the corresponding customer information will be pulled automatically.
  • Product Code : Once entered, the related product information will be retrieved automatically.
  • Quantity : Enter the quantity purchased.

2.3) Product Sheet

  • Product Code : Create your own product code that reflects the product type. For example, for a Rubberwood Round Table, the code can be FUR-TBL-RND (Furniture-Table-Round).
  • Product Name : Enter the name of the product.
  • Unit Price : Enter the selling price per unit.
  • Discount : Enter the discount rate (this will be used for automatic calculation on the Invoice sheet).

2.4) Customer Sheet

  • Customer ID : Assign a unique running number for each customer.
  • Name : Enter the customer’s first name.
  • Surname : Enter the customer’s last name.
  • Address : Enter the full address customer’s phone number.
  • Tax ID Number : Enter the customer’s tax identification number.

3.) How to use this PDF invoice generator (Excel VBA Template)

3.1) Customize the Template on the first sheet

Adjust the layout to suit your company’s branding and details. You can modify:

  • Company Logo
  • Company name and address
  • Remarks section
  • Salesperson name
  • Payment terms and due date

3.2) Customize product information

  • Update the product details to match your company’s offering. (For example, in this template, I use sample data from a furniture store).

3.3) When a new customer places an order

  • Enter the new customer’s information into the Customer Data sheet.
  • Fill in the order details in the order Entry section. Make sure the Customer ID and Product Code match the existing records in the customer and product sheets.

3.4) When an existing customer places an order

  • No need to re-enter the customer’s information, as it already exists in the customer data sheet.
  • Simply fill in the order details in the order entry section. Ensure that the customer ID and product code match the existing records in the customer and product sheets.

3.5) Generate a single invoice PDF

  • Choose your desired save location and paste the path into cell X4
  • Enter the Delivery Note Number in the invoice sheet.
  • Click Run Invoice PDF Individual to generate and save the invoice as a PDF file.

3.6) Generate Multiple invoices PDF

  • Choose your desired save location and paste the path into cell X4
  • Enter the Delivery Note Numbers you want to process into column U
  • Click Run Invoice PDF Batch

4.How to code Automatic Invoice Excel VBA

  • Sub RunInvoicePDFIndividual()
Sub RunInvoicePDFIndividual()
   Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
   Range("X5") & "\" & Range("X4") & ".pdf", Quality:=xlQualityStandard _
   , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
   False
End Sub
  • Sub RunInvoicePDFBatch()
Sub RunInvoicePDFBatch()
    n = WorksheetFunction.CountA(Columns("U")) - 1
    For i = 1 To n
        Range("U" & i + 2).Select
        Selection.Copy
        Range("J10").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            Range("X5") & "\" & Range("X4") & ".pdf", Quality:=xlQualityStandard _
            , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            False
    Next
      MsgBox "Run Invoice PDF Batch Complete", vbInformation
End Sub
  • Sub Clear()
Sub Clear()
    Range("J10") = ""
End Sub

5.Please enter your email below to instantly receive the download link for the Excel VBA Automatic PDF Invoice Template!

6.Video tutorial: How to create automatic PDF invoices using Excel VBA

I hope this article is helpful for anyone who wants to create automatic invoices using Excel VBA.
If you have any questions, feel free to leave a comment or reach out to me directly.
See you in the next article!

You can find more the article below:

Consult the data

Consultant and trainer in Excel, Power BI and Data Storytelling. Feel free to contact me via Line Official Account.