October 4, 2017

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

Download Sample File for Practice:

15 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.

  2. please guide me to finish my project but when i am started to make vissual basic its showing red collor letters and its telling compile error i dont understand this error

    1. I had the same problem. Finally I discovered it was in the “,” between the B3 and B (and further on this was an issue with all the comma’s). I retyped them and it was okay after that. So you just retype every:
      ,
      ;
      ‘ ‘
      ” ”
      I have no idea what the exact explanation is, but to me it worked. Probably something with systemlanguage, or fonttype???

      1. Sorry, wrong information: it was the “:” between the B16 and B. Where the range was set…
        That one needed to be an “,”.

  3. Hi
    I am trying to use the code for Save Inventory. I have copied you code and can’t get it to work. I have redone the code and it still won’t work. Please can you help?
    See below code, i have tried to use the same column and rows you have in you example.

    Sub UpdateInventorys()
    ‘We declair 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 Range2 = 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.Ofset(0, 2)
    End If
    Next cell2
    Next cell1
    End Sub

    1. I can’t get this code to work. Once debug
      lastRow1 = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
      I’m getting Run-time Error ‘9’ with a Subscript out of range. please if any one can help solve the problem

      1. I got it working for my needs. The minor changes in the code:

        Sheets(“Sheet1”).Range –>Sheet1.Range same for the other sheets. Works perfectly.

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

        Dim lastRow2 As Long
        lastRow2 = Sheet3.Range(“A” & Rows.Count).End(xlUp).Row
        Set rng2 = Sheet3.Range(“E7:E” & lastRow1)

        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, 5) = cell2.Offset(0, 5) – cell1.Offset(0, 1)
        End If
        Next cell2
        Next cell1
        End Sub

  4. I have got it working for the first cell B2, the values change. but the other cells don’t change. Please Help!!

    I sent a message earlier but looked on you chat line and found some answers. It seems to me the loop is not working.

    Sub updateInventorys()
    ‘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

  5. How ‘s the code if the data has multiple columns in quantity in inventory?, because my inventory is about shoe sizes.

  6. A little help .
    I have made a great invoice works very good.
    I have made a great inventory works very good.
    Only by there self but not together as a unit.
    The invoices need to control the inventory.
    I made some what way to save the invoice history.
    I need it all to come together.
    I did the formulas they work well .
    I can’t a program already made
    Will you give some advice???
    JR

  7. I wish there was a place I could buy the inventory & invoice software in excel I due a little but can’t make them work as one.. at a time is all I can do

Comments are closed.