Using Invoice Template to Automate Invoice Creation and Printing with VBA

Somebody rightly said: All cash flow starts with an invoice. We can use an invoice template to automate the invoice creation and printing with VBA.

  • First we download an invoice of our choice right from within MS Excel when we start the program or software
  • Next we save it as a normal file ‘.xlsx’ file
  • Now we study the various elements of the invoice which we can fill out automatically using VBA
  • We copy the elements headings and paste them in our Excel worksheet
  • Next we enter the data as we make the sales
  • We place a command button on our worksheet by clicking on the developer tab, selecting the insert option and from the ActiveX controls we click on the ‘command button’ icon. We can now click and drag to draw a command button at a place of our choice on the Excel worksheet. We right-click on the command button, select properties and from the new window that pops up we select the text box next to the item caption and insert our text, for example, print invoices
  • We double click on the command button in the deign-mode and between the two lines of code that are shown in the Visual Basic for Applications Editor (VBE) we start writing our own code
  • First we define the different variables that we will need
  • Now we get the data from the worksheet cells into our variables
  • We now assign the variables to the relevant worksheet cells in the invoice file
  • Since the variables already have values from the worksheet stored in them, these values are assigned to the invoice worksheet cells.
  • We have mapped a few interesting items. The others you can do as an exercise to firm up your understanding of VBA
  • Once the data is entered into the invoice worksheet cells automatically we save the file in a specific manner using the path to the directory, the invoice number, customername, etc
  • Then we make the file read only
  • Also we place automatically a text value called ‘done’ under the header in our Excel data worksheet
  • This ensures that these files will not be saved and printed again by our program or macro

The complete VBA code is given below:

Private Sub CommandButton1_Click()

‘First we define a few variables to capture data from our Excel worksheet
Dim customername As String
Dim customeraddress As String
Dim invoicenumber As Long
Dim r As Long
Dim mydate As String
Dim path As String
Dim myfilename As String

‘our last row of data in the worksheet is defined
lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

‘ we start at row 2 since the first row contains headers
r = 2

‘Looping process starts
For r = 2 To lastrow

‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

If Cells(r, 17).Value = “done” Then GoTo nextrow

‘we map excel worksheet data to the variables

customername = Sheets(“CustomerDetails”).Cells(r, 1).Value
customeraddress = Sheets(“CustomerDetails”).Cells(r, 2).Value
invoicenumber = Sheets(“CustomerDetails”).Cells(r, 6).Value
quantity = Sheets(“CustomerDetails”).Cells(r, 18).Value
Description = Sheets(“CustomerDetails”).Cells(r, 19).Value
UnitPrice = Sheets(“CustomerDetails”).Cells(r, 20).Value
SalesTaxRate = Sheets(“CustomerDetails”).Cells(r, 16).Value

Cells(r, 17).Value = “done”
Application.DisplayAlerts = False
Workbooks.Open (“C:\invoices\BasicInvoice.xlsx”)

‘ now we map the variables to the invoice worksheet data
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“I8”).Value = invoicenumber
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C8”).Value = customername
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C9”).Value = customeraddress
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“B21”).Value = quantity
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C21”).Value = Description
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“H21”).Value = UnitPrice
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“D18”).Value = SalesTaxRate

path = “C:\invoices\”
mydate = Date
mydate = Format(mydate, “mm_dd_yyyy”)

‘File is saved as a read-only file
ActiveWorkbook.SaveAs Filename:=path & invoicenumber & “-” & customername & “-” & mydate & “.xlsx”
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut copies:=1

‘We close the active workbook. We don’t need to save it because we just saved it!
ActiveWorkbook.Close SaveChanges:=False

‘our label next row. Labels have a colon after their name


Next r

End Sub

View the training video. If you are not able to view the data conveniently then use the full-screen mode.


Watch the video on YouTube.

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

23 thoughts on “Using Invoice Template to Automate Invoice Creation and Printing with VBA”

  1. Hello- I found your youtube posts and found them very helpful. I have a quick question and was wondering if you could email me (I can’t find your contact information here).


  2. Hi sir,

    First, I would like to say thank you so much ..! for your innovative ideas for teaching this..

    Can we do the below scenario through VBA? is it possible ?

    I need to create a macro for employee working hours calculation report.

    step 1: When i start the system the macro will popup up commend button automatically & ask please enter Employee name & Employee ID ? once done this it will calculate working hours..

    step 2: In between time if we lock the system it will consider as a break time( 10 min & 20 min=30 min

    step 4: when we are going to shut-down the system. it will consider as end time. message box will popup up your time is end.

    step 5; Finally we need to get total working hours.

    Employee name / Employee ID / Start time & date / End time & Date / break hours / total working hours

    xxxx /546 / 5/20/14 12:00 AM / 5/20/14 4:00 AM / 10 min+ 5 min+15 min = 30min/ 3:30 hrs

    Note: No one can not edit the system updated timings by manually.
    if there are 10 employees, all the 10 employees working hours will be appear in the single excel sheet.

    finally the file will save the data in particular location automatically.
    Can you help me this ?


  3. Hi Dinesh,

    thank you for a wonderful video. I have tweaked the code and used it in my work. But then I am facing issues which I would like your help with. Could you please let me know if I can contact you in anyway.

    Thank you.

  4. Dear Sir,

    This is awesome, is there a way where we can transfer the excel content in Word and save the invoices individually?

    It is the same way just in place of transferring data from excel to excel we have to transfer the data to word document.

    Please suggest and Thank you so much for all the knowledge.


  5. Hi Dinesh

    Is there a way with your code above to reverse the process?

    Once I have the created the invoice and have saved the data into a second spreadsheet for record keeping, how can I reverse it all and create the invoice again (say if the customer requests another copy in a months time)

    I’d like the user to press a button, this opens up a input box where they can insert the invoice number or their name and the user is presented with the results, they then choose which invoice they wish and then it repopulates the invoice with all the original data and prints again.


  6. Hi Dines
    am new to VBA. I have been watching your video and learn a lot stuff. I have created useform and database using your codes. they work to the prefection. My next time is how can i automatically consolidate the data. For example My vbais used for a farmer, He has his produce that he distributes to different seller, He want to be able to consolidate the data and print invoice and shipping label. I am having difficulty creating the codes for this. Is there anyway you can help or point me in the right direction. I can also send you the the file that i have so far.
    Thank you

  7. Hi Sir,
    Thanks for the lesson, very helpful.
    I have some trouble of using looping in my exercise. It looks like there is bug in my spreadsheet. Would you mind helping! thanks!!!

    ‘our last row of data in the worksheet is defined
    lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

    ‘ we start at row 2 since the first row contains headers
    r = 2

    ‘Looping process starts
    For r = 2 To lastrow

    ‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

    If Cells(r, 17).Value = “done” Then GoTo nextrow

    1. i have some tourble
      plaese solve this problem with example

      our last row of data in the worksheet is defined
      lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

      ‘ we start at row 2 since the first row contains headers
      r = 2

      ‘Looping process starts
      For r = 2 To lastrow

      ‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

      If Cells(r, 17).Value = “done” Then GoTo nextrow

  8. Can you please help us, we are getting error while performing the same steps.

    can you please me the sample sheet?

  9. Hi sir, I think your information is pretty much to the point. I used this program and everything works fine, except that the numbers in the report are coming out as $5,891.00 instead of $5,890.65. I have tried changing the format with different ways, but to no avail. Do you have any ideas? Thank you.

  10. Thanks a lot again (I should had said this before) for taking the time to explain these concepts to us would-be-programmers. I really enjoyed doing these things and love when they work properly and make the job easier. Thank you.

  11. Hi ! I have a worksheet named invoice which is linked to another worksheet in the same workbook. I want to add a print button to the invoice sheet so that on clicking the button, a new workbook is created with just the invoice sheet containing only data and formatting ( no links ). The new workbook should be saved at the same location as the original file. The new workbook should be named from a cell reference in the original invoice worksheet. Please help me out as I have zero knowledge of macros, etc.,
    Thanks. Vikram

  12. hello sir

    i have amended code to my needs, unfortunately nothing is happenning when I activate marko.

    coudl you please help?


  13. Hi Dinesh,

    Thanks a lot for sharing this.

    I got just 1 question in the above solution. How do we print/save our individual invoices in PDF format other than xlxs? I use latest Microsoft Office in Mac and also in Windows.

    Please suggest. Will really appreciate your help.

    Many Thanks,

  14. Hello!

    This is really really a good code…… I have used it for managing some other Template and worked very well.

    Thanks a lot.


  15. Hi Dinesg,

    I have found your youtube video very helpfull and educational. Thankyou for posting.

    However, I have an error in my last row code. I have followed pretty much all your instructions and am not sure where the error is. Are you able to kindly direct me in the rigirection:

    Lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).Row.

    Kind Regards

  16. HI Thank you so much, I have been watching many of your videos and reading your posts. Very helpful.
    The only thing is this doesn’t explain how to loop multiple inventory sales down from a spreadsheet that has multiples products sale for one customer. So I can only get one line of products to show on the invoice automatically but cannot seem to loop the next inventory line for the same supplier.

    Can you please advise an additional loop code that will get multiple inventory sales lines on the one invoice automatically. Thank you so much.

  17. hi sir i get the runtime error 9 subscript out of range
    lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).Row

    any help

Leave a Reply

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