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:
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.
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.
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.
Let’s prepare the database
I have populated Sheet2 and 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
Leave a Reply