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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! 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: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Automatically Update Inventory while Creating Invoice”

  1. Hello sir, thanks for your guidance and i salute you for your great knowledge.
    Sir i am trying to make stock data but stuck at one point.
    I have a small factory where I 1.purchase raw material and make parts from it.
    2. I also purchase some other ready parts from market and
    3. I assemble these parts and make a product.
    So I have three stages of stock. Now what I want, when i invoice a assembled product then the stock of parts used in making it should also reduce.
    Example. I make two assembled products named ZA and ZB. To make ZA I need 4 quantity of part A1, 5 of A2, 3 of A3. And for ZB I need 2 of A2, 4 of A4 and 1 of A5.
    I want to enter both assembled item in one invoice and need automatic updation of stock

    I also have a second query that is it possible that I type the path of a folder where i want to save the invoice in a cell of worksheet and code will capture it and save the invoice in that folder…
    I need this for that if i want to change the destination folder then i just put another path in the same cell without going into the code.

    Sir, waiting for your reply.
    Thanks with regards.

Leave a Reply

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