Automate Invoice Report Generation Using Excel VBA


A user would like to automatically generate a report of her invoice in another Excel worksheet once she has finished entering all the relevant data in the invoice template. The invoice template performs all the calculations like totals automatically using in-built Excel formulas and functions. We can automate the invoice report generation using Excel VBA. Once the user runs the Excel VBA code a report is generated in sheet2 and the file is saved in two formats PDF and XLSX. Now the user can make new entries in the same invoice template to create a new invoice and run the VBA code. In this manner he not only generates the report in Sheet2 but can also archive the invoices in PDF and XLSX formats. In sheet2 he generates a hyperlink to the PDF file which he can view with a single click.

View the training video below to understand the complete process of automating the invoice report generation with Excel VBA:

View the video on YouTube.


Download a sample file by clicking on the Excel icon:


Here’s the complete VBA code:
Sub InvoiceReport()
Dim myFile As String, lastRow As Long
myFile = “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & Format(Now(), “yyyy-mm-dd”) & “.pdf”
lastRow = Sheets(“Sheet2”).UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
‘Transfer data to sheet2
Sheets(“Sheet2”).Cells(lastRow, 1) = Sheets(“Sheet1”).Range(“B5”)
Sheets(“Sheet2”).Cells(lastRow, 2) = Sheets(“Sheet1”).Range(“F1”)
Sheets(“Sheet2”).Cells(lastRow, 3) = Sheets(“sheet1”).Range(“I36”)
Sheets(“Sheet2”).Cells(lastRow, 4) = Now
Sheets(“Sheet2”).Hyperlinks.Add Anchor:=Sheets(“Sheet2”).Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
‘Create invoice in PDF format
Sheets(“sheet1”).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = False
‘create invoice in XLSX format
ActiveWorkbook.SaveAs “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & “_” & Format(Now(), “yyyy-mm-dd”) & “.xlsx”, FileFormat:=51
‘ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Further reading:
Use VBA SaveAs in Excel 2007-2013