Automatically Update Inventory while Creating Invoice

How to automatically update inventory while creating invoice. The idea here is to link the creation of a new invoice with the inventory data which gets automatically updated. Before we automatically update the inventory data, we need to allow the user to save his invoice. The file saving is done using a text function which extracts a unique number called the Goods and Services Tax (GST) number and the invoice number. In this manner we create a unique file name. She may also need to print out the invoice. Then we update the inventory data in sheet2. Finally we clear specific areas of the invoice for data entry for a new invoice.

Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code:

Sub saveInvoice()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = “C:\invoices\”
FileName1 = Right(Range(“A9”), 23)
FileName2 = Range(“B13″)
Sheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ” – ” & FileName2 & “.pdf”, OpenAfterPublish:=True

End Sub

Sub printInvoice()
Sheet1.PrintPreview
‘sheet1.printout
End Sub

Sub updateInventory()
‘ we declare 4 variables
Dim rng1, rng2, cell1, cell2 As Range
Dim lastRow1 As Long
lastRow1 = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Set rng1 = Worksheets(“Sheet1”).Range(“B16:B” & lastRow1)

Dim lastRow2 As Long
lastRow2 = Sheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Row
Set rng2 = Worksheets(“Sheet2”).Range(“A2:A” & lastRow2)

For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For

For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1 = cell2 Then
cell2.Offset(0, 1) = cell2.Offset(0, 1) – cell1.Offset(0, 2)
End If
Next cell2
Next cell1
End Sub

Sub clearData()
Dim lastRow As Long
lastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Range(“A9:G11”).ClearContents
Range(“D13”).Clear
Range(“A16:F” & lastRow).Clear
Range(“B13”) = Range(“B13”) + 1
End Sub