How to Make a Free Invoice Generator with Excel

If you are tracking projects and payments in a simple excel sheet, follow this article and convert your Excel workbook into an automated invoice generating tool. With a single click, instantly prepare invoices for all your clients.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

We need 3 sheets. Sheet1 will carry the invoice template. You can create your own template or use free templates available on the internet.Sheet1 To Carry The Invoice Template

In Sheet2 we will be saving Clients’ details. For demo purpose, I have included ClientName, Address1, Address2, Phone and Email. You can include additional fields.Saving Clients' Details In Sheet2

In Sheet3 we will be saving Items purchased by Clients. As shown in the image, each transaction by the Client will be recorded on this sheet along with the date.Saving Items Purchased By Clients In Sheet3

Let’s prepare the database

I have populated Sheet2 and Sheet3 with necessary data.Populated Sheet2 With Necessary Data

Populated Sheet3 With Necessary Data

Let’s make it functional

Copy the script to a new module in your workbook. You do not need to create any additional buttons to attach this macro.

From Sheet2, select a Client name and run the macro “Update_Invoice”. All transactions by the Client will be moved to the Sheet1 i.e., Template sheet and a PDF would be generated and saved with selected Client’s name. The newly generated PDF file will be saved in the folder where this macro enabled workbook is saved.

How does it work?

When you run the macro, the macro will identify the active cell and reads the Client’s name from it. The template is now filled with Client’s name, address, phone, fax, and email. The macro loops through each row in Sheet3 and fetches all transactions for the selected Client. The loop stops when there is an empty row. So please make sure that there are no empty rows in between the Sheet3. This will stop the macro in between resulting in a partially generated invoice. As soon as a matching transaction is found, it is moved to the template. The Sheet1 is saved as PDF file which is the final step. The template should be designed in such a way that it fits within the margins of A4 size paper. You can use Page Break view to fix the margins and the template.

Modify it?

As of now the macro works for a single selection. You can modify the macro to work on multi-selected Clients. A new folder with Client’s name can be created to save newly generated PDF files. Export the Module1 as Module1.bas which you could use in future for recovering damaged Excel file.

Script:

Sub Update_Invoice()
    Dim v_row As Integer
    v_row = ActiveCell.Row
    Sheets("Template").Range("B9").Value = Sheets("Client").Range("A" & v_row).Value
    Sheets("Template").Range("B10").Value = Sheets("Client").Range("B" & v_row).Value
    Sheets("Template").Range("B11").Value = Sheets("Client").Range("C" & v_row).Value
    Sheets("Template").Range("B12").Value = Sheets("Client").Range("D" & v_row).Value
    Sheets("Template").Range("B13").Value = Sheets("Client").Range("E" & v_row).Value
    Dim v_lrow As Integer
    v_lrow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    Dim v_tempr As Long
    v_tempr = 14
    Dim r As Long
    For r = 2 To v_lrow
        If Sheets("Database").Range("B" & r).Value = Sheets("Client").Range("A" & v_row).Value Then
            v_tempr = v_tempr + 1
            Sheets("Template").Range("B" & v_tempr).Value = Sheets("Database").Range("C" & r).Value
            Sheets("Template").Range("C" & v_tempr).Value = Sheets("Database").Range("D" & r).Value
        End If
    Next r
    Sheets("Template").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & Sheets("Client").Range("A" & v_row).Value & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
End Sub

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word and outlook recovery software products. For more information visit www.datanumen.com

Comments are closed.