How to transfer data to an invoice template

How to transfer data to an invoice template in Sheet2, perform calculations and save the invoice worksheet as an Excel and PDF file.

This training video is based on a query by a viewer of my YouTube channel. He runs a company that collects rent for hundreds of apartments. He enters raw data into sheet1 and in sheet2 he has an invoice template. Now he wants to automatically enter data into the invoice template from sheet1, perform calculations and save only the invoice sheet in Excel and PDF formats.

Watch the training video before you study the VBA code:

When we open the workbook we remove all the data from the relevant cells, save the file and  ensure that our invoice number is incremented by one. Here’s the VBA macro code:

Private Sub Workbook_Open()
Sheet2.Range(“F2”) = “”
Sheet2.Range(“F3”) = “”
Sheet2.Range(“A6”) = “”
Sheet2.Range(“F11:F25”) = “”
Sheet2.Range(“F4”).Value = Sheet2.Range(“F4”).Value + 1
ActiveWorkbook.Save
End Sub

The ‘Get Data’ command button performs the following actions to automate the whole process:

Sub getDataSheet1()
Dim erow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets(“Sheet1”)
Set ws2 = Worksheets(“Sheet2”)
erow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim tenantno As String
tenantno = InputBox(“Enter tenant number”)
For i = 4 To erow
If ws1.Cells(i, 1) = tenantno Then
ws2.Range(“F2”) = ws1.Cells(i, 1)
ws2.Range(“A6”) = ws1.Cells(i, 2)
ws2.Range(“F12”) = ws1.Cells(i, 8)
ws2.Range(“F13”) = ws1.Cells(i, 9)
ws2.Range(“F22”) = ws1.Cells(i, 19)
ws2.Range(“F23”) = “=sum(F12:F22)”
ws2.Range(“F24”) = ws2.Range(“F23”) * 0.06
ws2.Range(“F25”) = ws2.Range(“F23”) + Sheets(“Sheet2”).Range(“F24”)
Dim Path As String, mydate As String
ws2.Range(“F3”) = Date
mydate = ws2.Range(“F3”)
mydate = Format(mydate, “mm_dd_yyyy”)

Path = “C:\ABC-Properties\”
Application.DisplayAlerts = False
ActiveWorkbook.ActiveSheet.SaveAs Filename:=Path & Range(“F2”) & “-” & Range(“A6”) & “-” & mydate & “.xlsx”, FileFormat:=51
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & Range(“F2”) & “-” & Range(“A6”) & “-” & mydate & “.pdf”, OpenAfterPublish:=False
‘ActiveWorkbook.Close SaveChanges:=False
End If

Next i
‘ MsgBox myfilename
Application.DisplayAlerts = True

ActiveWorkbook.ActiveSheet.Shapes(“Button 1”).Delete
Application.Quit
ActiveWorkbook.Close SaveChanges:=True
End Sub

The important thing to notice here is how we save the file using the values in the different cells of the invoice. In fact, we convert the date from  09/14/2015 format to the string format 09_14_2015 so that this string can also become the part of the file name. Slashes like ‘/’ are not acceptable in file names in the Windows environment.

Further reading:

How to select a control on a worksheet

Excel Quit Method