Once you have a GST number, you need to file your tax return every month (in India). So every month, my Charted Accountant would reach out to me asking for sales invoices so that he can file for the GST. In my case, there only a handful of invoices to be created, as I have only a few sources of income. However, since this is additional work, I wanted to get this done as quickly as possible. So to minimize my effort, I created an Invoice Generator template in Excel that allows me to have all the data in one place, and then it automatically creates PDF invoices for all the data points. As you can see below, all I need to do is double-click on the client’s name (in column B), and it would instantly create and save the invoice in the PDF format in the specified folder.
You can also modify the invoice template to suit your company’s format (which would require you to change the VBA code a little – explained below). It creates an invoice as shown below:
How does this Invoice Generator Template Works?
In this invoice template, there are two worksheets: I also have a folder on my desktop with the name ‘Invoice PDFs’. This is the folder where the newly created PDF invoices are saved. Now let’s see how this works: You need to double-click on the client’s name (highlighted in orange in the Details sheet). That’s it! When you double-click on the client name, it kickstarts the VBA magic in the back end and the following things happen: In case there are any changes in the invoice details, you can double-click on the client name again, and a new invoice will be created (and this will overwrite the old one). Note that the names of the invoices are based on the month and the invoice number. For example, an invoice with the date 15-04-2019 and the invoice number as 1 would be saved with the name April 2019_1.pdf. This helps in keeping track of the invoices in case you have too many. You can download the Invoice Generator Template by clicking on the button below:
Modifying the Invoice Generator Template
I created this invoice template with a format that I needed for my GST filings. If you need a different format, you’ll have to edit the template and then adjust the backend VBA code. Let me first show you the code and explain how it works: The above code is what copies the details of a transaction, fills the invoice placeholder template with those details, creates a new workbook, and save the new workbook as a PDF in the specified folder. If you have a different template or a different folder location, you need to modify the below-highlighted parts of the code:
Note that I have renamed the worksheet code name to ‘shDetails’. I have done this so that I can use the name – shDetails – in my code and it would continue to work even if you change the name of the sheets in the worksheet. If you want to learn more about the sheet name and the code name, have a look at this (check out the section on using the Worksheet code name).
Where is the code in the workbook?
The code is placed in the back-end of the Excel workbook in a module. To access the code, follow the below steps: In case you’re creating a template yourself, you may not find the Module in a new workbook. You need to right-click on any of the workbook objects, go to Insert, and then click on Module. This will insert a new module.
Making the Double-click functionality to Work
The above code does all the heavy lifting, but you need to connect it to the double-click event. This means that the above VBA macro code needs to run whenever someone double-clicks on the filled cells in the client name column. This can be done by inserting the following code in the worksheet code window: Here are the steps to insert this code in the worksheet backend:
Right-click on the ‘Details’ worksheet tab Click on the ‘View Code’ option. Copy and paste the above code into the code window that appears.
The above code does the following things: Click here to download the Invoice Generator Template file.
Saving the Invoice Template as Excel (instead of PDF)
If you want to save the invoice templates as Excel files and not as PDFs, you can use the below code instead: The above code saves the invoice as an Excel workbook with the same naming convention. The worksheet in the workbook that contains the filled invoice in each saved workbook is also named the same.
How to Convert Excel to PDF Using VBA. Embed PDF in Excel. Extract Data From PDF to Excel with this Converter. Excel Timesheet Calculator Template. Excel Leave Tracker Template. Free Excel Templates.
Debug – sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ FPath & “” & Fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False I have no faintest clue what is the above… LOLz Really nice job btw.
