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
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
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


One thought on “How to transfer data to an invoice template

  1. Mohammed


    How to transfer (specific data) from current EXCEL to another. but just specific data to anther worksheet – to
    again specific data field as progammed to do so on the vba. so eg

    workbook 1 (data cell. A1 and B1)


    workbook 2 (to be coppied into cell A1 and B1) and it carrys on adding on the next empty cell only previous all data
    which are automaticlly coppied here remains the saim unless manully edited.

    so it needs to be ongoing from another link workbook file – so i would enter new details on (workbook 1) and it will copy
    to workbook 2 after (a button has been pressed on workbook 1)

    at the same time is it possible to VBA code so that “SaveAss button” button if you like to save in specific file/folder.

    so workbook 1. on the bottom somewhere it will have “SaveAss button” which should do the following:

    1. save file on a specific folder using (a invoice number or name from a cell)

    2. copy certain data from (workbook1) certain cell to (workbook2)

    3. once the SaveAss button is pressed (it should clear certain data entered)

    Please help if you can.

    i have been working on some coding i can send you what i have so far and i just need the 2. now and 3.


Leave a Reply

Your email address will not be published. Required fields are marked *