September 14, 2015

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

8 thoughts on “How to transfer data to an invoice template


    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.

  2. Hi Sir, I am following you for past couple of month and seriously it was been wonder fully journey for me having watch most of yours video which make my work & performances more accurate & fast in past month and half . I had a sale report from which certain data has to be exported to create automatic invoice. How can I send u that file .

    Once thank for generous help for improving our skill.

  3. Sir,
    Request to send me this exel sheet .
    I am looking for similar system exel vba programming exel sheet

    Milind dhake

  4. sir the second i am writing behind a button , i.e, code for a button. but its showing compile error. where do i have to write the 2nd part of code ?

  5. Sir I have 1 question how will I assign hyperlink automatically using vba codes to invoice tracker’s data to the directory where invoice saved. After entered data from userform

  6. Hi Sir, Thank you for your contribution to the public. May I ask if we can replace the get data prompt to a drop down list (decending order) for us to choose instead of key the tenant number.

    thank you

Comments are closed.