Invoice Tracker

How to create invoices and track the data using VBA. Watch the video before studying the VBA code step by step from the various modules assigned to their respective forms controls on the worksheet:

The following buttons are assigned to the following macros in their respective Modules:

Module1 has the macro or sub routine ‘CreateInvoice’ which is assigned to the form control ‘Create Invoice’ on the worksheet with the following VBA code:

Public taxRate As Single
Public advance As Single
Public lastRow As Long
Sub CreateInvoice()

Set wsInvoice = Worksheets(“Invoice”)
lastRow = wsInvoice.Cells.Find(What:=”*”, After:=Range(“A1”), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Dim i As Long

For i = 9 To lastRow
‘On Error Resume Next
Cells(i, 7).Value = Application.WorksheetFunction.IfError((Cells(i, 4).Value * Cells(i, 5).Value – Cells(i, 6).Value), “”)
Next i

lastRow = lastRow + 1

wsInvoice.Cells(lastRow, 6).Font.Bold = True
wsInvoice.Cells(lastRow, 6) = “Invoice Subtotal”
Cells(lastRow, 7).Value = Application.WorksheetFunction.Sum(Range(Cells(9, 7), Cells((lastRow – 1), 7)))

lastRow = lastRow + 1

‘On Error Resume Next

taxRate = InputBox(“Enter the tax rate as a number like 12, 18, 12,5”, “Tax Rate”)

‘If Err.Number Then
‘Exit Sub
‘End If

Cells(lastRow, 6).Font.Bold = True
Cells(lastRow, 6) = “Tax Rate”
Cells(lastRow, 7) = taxRate & “%”

lastRow = lastRow + 1

Cells(lastRow, 6).Font.Bold = True
advance = InputBox(“Enter the advance received”, “Advance Received”)
Cells(lastRow, 6).Value = “Advance received”
Cells(lastRow, 7).Value = advance

lastRow = lastRow + 1

Cells(lastRow, 6).Value = “Invoice Total”
Cells(lastRow, 6).Font.ColorIndex = 5
Cells(lastRow, 6).Font.Bold = True

Cells(lastRow, 7).Value = Cells(lastRow, 7).Offset(-3, 0) + (Cells(lastRow, 7).Offset(-3, 0)) * (taxRate / 100) – Cells(lastRow, 7).Offset(-1, 0)
Cells(lastRow, 7).Value = Round(Cells(lastRow, 7).Value, 2)

lastRow = lastRow + 1

Cells(lastRow, 3).Value = “Make all checks payable to company name.”

lastRow = lastRow + 1

Cells(lastRow, 3).Value = “Total due in 15 days. Overdue accounts subject to a service charge of 1.5% per month.”

Cells(lastRow, 3).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

Module2 has the macro or sub routine ‘createInvoiceReport’ which is assigned to the form control ‘Create Invoice Report’ on the worksheet with the following VBA code:

Sub createInvoiceReport()

createInvoicesReport = InputBox(“Do you wish to create Invoice Repor in the InvoicesReport sheet? Enter y or yes to do so”, “Copy Customer’s Data”)
Set wsInvReport = Sheets(“InvoicesReport”)
lastrowInvReport = wsInvReport.Range(“A” & Rows.Count).End(xlUp).Row
erowInvReport = lastrowInvReport + 1

If createInvoicesReport = “y” Or createInvoicesReport = “yes” Then
Range(“H4”).Copy wsInvReport.Cells(erowInvReport, 1)
Range(“B4”).Copy wsInvReport.Cells(erowInvReport, 2)
Range(“H5”).Copy wsInvReport.Cells(erowInvReport, 3)
wsInvReport.Cells(erowInvReport, 4) = taxRate
wsInvReport.Cells(erowInvReport, 5) = advance
wsInvReport.Cells(erowInvReport, 6) = Cells(lastRow, 7).Offset(-2,0)

End If

End Sub

Module3 has the macro or sub routine ‘openFolder’ which is assigned to the form control ‘open an existing invoice’ on the worksheet with the following VBA code:

Sub openFolder()
Shell “Explorer.exe C:\myinvoices\”, vbNormalFocus
Dim strFolder As String
strFolder = “C:\myinvoices\”
ActiveWorkbook.FollowHyperlink Address:=strFolder, NewWindow:=True
End Sub

Module4 has the macro or sub routine ‘saveInvoiceWorksheetAsFile’ which is assigned to the form control ‘save Invoice’Worksheet’As’File’ on the worksheet with the following VBA code

Sub saveInvoiceWorksheetAsFile()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets(“Invoice”).Copy Before:=wb.Sheets(1)
Dim fname, fpath
fname = Range(“G4”) & “-” & Range(“H4”)
fpath = “C:\myinvoices\”
Application.DisplayAlerts = False
wb.SaveAs fpath & fname, xlOpenXMLWorkbook
‘Delete all form controls
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes

‘ Forms controls will be deledt
If myshape.Type = 8 Then myshape.Delete

Next myshape
End Sub

Module5 has the macro or sub routine ‘storeCustomerData’ which is assigned to the form control ‘store Customer Data’ on the worksheet with the following VBA code:

Sub storeCustomerData()
copyCustomerData = InputBox(“Do you wish to copy customer’s data to the customer’s sheet? Enter y or yes to do so”, “Copy Customer’s Data”)

Set wsCust = Sheets(“customers”)
lastrowCustomers = wsCust.Range(“A” & Rows.Count).End(xlUp).Row
erowCustomers = lastrowCustomers + 1

If copyCustomerData = “y” Or copyCustomerData = “yes” Then
Range(“B4”).Copy wsCust.Cells(erowCustomers, 2)
Range(“B6”).Copy wsCust.Cells(erowCustomers, 3)
Range(“B5”).Copy wsCust.Cells(erowCustomers, 4)
Range(“E4”).Copy wsCust.Cells(erowCustomers, 5)
Range(“E5”).Copy wsCust.Cells(erowCustomers, 6)

If wsCust.Cells(lastrowCustomers, 1) <> “” And wsCust.Cells(lastrowCustomers, 1) = “CompanyID” Then
wsCust.Cells(erowCustomers, 1) = 1
wsCust.Cells(erowCustomers, 1) = wsCust.Cells(lastrowCustomers, 1) + 1
End If

End If
End Sub

Module6 has the macro or sub routine ‘storeInvoiceDetails’ which is assigned to the form control ‘store Invoice Details’ on the worksheet.

Sub storeInvoiceDetails()
captureInvoiceDetails = InputBox(“Do you wish to capture Invoice Details in the InvoiceDetails sheet? Enter y or yes to do so”, “Capture Invoice Details”)

Set wsInvDetails = Sheets(“InvoiceDetails”)
lastrowInvDetails = wsInvDetails.Range(“A” & Rows.Count).End(xlUp).Row
erowInvDetails = lastrowInvDetails + 1

If captureInvoiceDetails = “y” Or captureInvoiceDetails = “yes” Then
For i = 9 To (lastRow – 6)
Range(“H4”).Copy wsInvDetails.Cells(erowInvDetails, 1)
Range(Cells(i, 2), Cells(i, 7)).Copy wsInvDetails.Cells(erowInvDetails, 2)
erowInvDetails = erowInvDetails + 1
Next i
End If
End Sub

Further Reading

Deleting shapes

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:

5 thoughts on “Invoice Tracker”

  1. Dear Dinesh Kumar Takyar

    Please kindly explain 6th Module

    For me as per guidelines not working storeinvoicedetails

    when i run the macro no error found but nothing copy

  2. Hello sir
    Have a great day
    Please allow me to download this invoice tracker
    So that I can understand this in a easy way
    Best regards a K Sharma

  3. Dear Sir,
    Greeting of the Day.
    Thanks for your valuable & quick Response on my Comment of Invoice Backup.Sorry for late reply from my side.Sir it is working very nicely,but I dont want this type of backup.sorry its my mistake in asking question wrongly,I want just transfer data to next sheet but condition is that,
    1)Date,Invoice No,Item , discription,Amount,Sale Type,GST Rate,this should be transfer according to invoice Items Fill Rows,But Round off amount & Net Amount should be transfer only ones at the end of that particular Invoice for that particular date.How can we do this? It is possible in VBA? I saw your video a video on you tube (ARCHIVE INVOICE), in this video the net amount is below the Invoice,its like a screen shot of our Invoice,like this I want the Round off amount & Net Amount should be in the column.
    Should I share my Invoice sheet & Backup sheet to you for your Information? If yes,please share your mail address.
    Nitin Chitnis.

Leave a Reply

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