In the video post about the Invoice Tracker I had shown how to track invoices, save customer details, invoice details and create a report. In the process it was noted that although unique company IDs were being created, duplicate customer details were being saved. Today we learn how to avoid duplicate customer details in invoice tracker and also optimize the code for wrapping text in a cell and placing the invoice total correctly. Watch the video and then study the VBA code carefully before creating your own invoice tracker:

Watch this video on YouTube.

Here’s the complete VBA code to avoid duplicates in customers worksheet. Module5 shows this step by step.

Module1

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

Selection.WrapText = True

End Sub

Module2

Sub createInvoiceReport()
MsgBox taxRate
createInvoicesReport = InputBox(“Do you wish to create Invoice Repor in the InvoicesReport sheet? Enter y or yes to do so”, “Copy Customer’s Data”)
Worksheets(“Invoice”).Activate
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

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

Module4

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
wb.Sheets(“sheet1”).Delete
‘Delete all form controls
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes

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

Next myshape
wb.Save
wb.Close
End Sub

Module5

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
For i = 3 To lastrowcustomers

If wscust.Cells(i, 2) = Worksheets(“Invoice”).Range(“B4”) Then
Exit For
Else
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
Else
wscust.Cells(erowCustomers, 1) = wscust.Cells(lastrowcustomers, 1) + 1
End If
End If
Next i

End If

End Sub

Module6

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

Download a sample file for practice: