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:
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.
You are greatfull sir. pls send a xlsm practise copy
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
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???
Sorry, wrong information: it was the “:” between the B16 and B. Where the range was set…
That one needed to be an “,”.
how to do same do horizontal
use for garments size wise
please help
i want to some code for readymade garment inventory
same as stock update
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
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
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
Thanks a ton.
This slight change in code solved my problem too
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
How ‘s the code if the data has multiple columns in quantity in inventory?, because my inventory is about shoe sizes.
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
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