Track inventory with barcodes in Excel
Вставка
- Опубліковано 12 вер 2024
- Track inventory with barcodes in excel. Record purchases and sales with barcodes. Keep your inventory up to date. All templates, with code, are available for purchase for $50 USD
www.easyexcela...
Click this link to check out my one-on-one training www.calendly.co...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.co...
I am able to provide online help on your computer at a reasonable rate.
Turn your smartphone or tablet into a powerful POS
loyverse.com/
www.amazon.com...
I use a Blue condenser Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.c...
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
TWEET THIS VIDEO • Track inventory with b...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
Templates with code are available for purchase for $50 USD
www.easyexcela...
How to insert VBA code in Excel • How to insert VBA code...
code for scan sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Call receive
Application.EnableEvents = True
End If
End Sub
Sub receive()
Dim barcode As String
Dim rng As Range
Dim rown, lrow As Long
Dim qty As Long
barcode = Sheet3.Cells(2, 3)
qty = Sheet3.Cells(2, 2)
Sheet2.Activate
'is there a barcode
If barcode = "" Then Exit Sub
If barcode (does not equal)"" Then
'search for the barcode on the inventory sheet.
Set rng = Sheet2.Columns("A:A").Find(what:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
' send an error message if you do not find it
If rng Is Nothing Then
MsgBox "number not found"
GoTo ende
Else
'determine which row has the barcode
rown = rng.row
If qty (is greater than)= 1 Then
'add the qty to the columns
Sheet2.Cells(rown, 9).Value = Sheet2.Cells(rown, 9).Value + qty
Sheet2.Cells(rown, 8).Value = Sheet2.Cells(rown, 8).Value + qty
'copy the description information
Sheet2.Range(Cells(rown, 2), Cells(rown, 7)).Copy
Sheet3.Activate
'paste it on the lastrow of the scan sheet
lrow = Sheet3.Cells(Rows.count, 3).End(xlUp).row + 1
Sheet3.Cells(lrow, 3).PasteSpecial
'enter the barcode and the qty information
Sheet3.Cells(lrow, 1).Value = barcode
Sheet3.Cells(lrow, 2).Value = qty
'enter the date and time for when this happened
Sheet3.Cells(lrow, 9) = Date & " " & Time
Sheet3.Cells(lrow, 9).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
GoTo ende
End If
If qty (is less than)1 Then
'add the qty to the columns
Sheet2.Cells(rown, 10).Value = Sheet2.Cells(rown, 10).Value + Abs(qty)
Sheet2.Cells(rown, 8).Value = Sheet2.Cells(rown, 8).Value + qty
'copy the description information
Sheet2.Range(Cells(rown, 2), Cells(rown, 7)).Copy
Sheet4.Activate
lrow = Sheet4.Cells(Rows.count, 3).End(xlUp).row + 1
'paste it on the lastrow of the outgoing sheet
Sheet4.Cells(lrow, 3).PasteSpecial
'enter the barcode and the qty information
Sheet4.Cells(lrow, 1).Value = barcode
Sheet4.Cells(lrow, 2).Value = Abs(qty)
'enter the date and time for when this happened
Sheet4.Cells(lrow, 9) = Date & " " & Time
Sheet4.Cells(lrow, 9).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
GoTo ende
End If
End If
End If
ende:
'turn off the marching ants
Application.CutCopyMode = False
Sheet3.Activate
'clear the cells on the scan sheet
Sheet3.Cells(2, 2).ClearContents
Sheet3.Cells(2, 3).ClearContents
'select the qty cell on the scan sheet
ActiveWorkbook.Sheets("scan").Activate
Sheets("scan").Range("B2").Select '(and activate)
End Sub
![Inventory system with barcode scanner in Google Sheets [AUTOMATED]](/img/n.gif)
You are the best! Thank you so so much! Seriously, your vids are amazing. I really appreciate you are sharing this knowledge with the world!
Wow, thank you!
Another brilliant video. Thanks so much for sharing
Glad you enjoyed it
Hello Barb Henderson, I would like to add the (Name) beside the barcode on the scan sheet so that the name of the person using the barcode scanner can be entered. What could be added to the code for this? Thank you
In this video I have them add their id before they scan in ua-cam.com/video/I4QaNWi3RmE/v-deo.html
I need inventory control/management from warehouse to multiple locations, intake back, and add new stock. Can't find and don't know how to do it, everyone wants a monthly fee. I'm inventory control without any costs or POS.
check out this video
Hello, thank you for the video!
We gave the sheet a go, and it worked perfectly up until we realized that the incoming and outgoing sheets weren't keeping a list of the items. So we're only able to see the most recent item going in/out (it keeps track of inventory fine on the inventory sheet, but there is still no list of items in either the scan sheet or the outgoing sheet)
Any insight on what the problem might be is appreciated.
Thank you in advance!
All of the incoming and out going should be listed on these sheets
Thanks Barb, great tutorial. How can I add custom cost for each scan entry? also is it possible to keep a record of each scan entry with timestamp?
you can replace one of the columns with the price and the last column on the sheet already has the time code entered
Hello barb great video! I have few question, if this template can be linked to barcode reader and what is the process or does it automatically recognise the commands when scanned?
You hook it up similar to connecting a printer and yes it does recognize the commands
@@BarbHendersonconsulting thanks 🙏
Hey I’m a little confused about the purchased and sold shouldn’t that be the same number ?
No, many require both, You have the on hand value which is a combination of both
The VBA code doesn't work for me, it gives me the error 424 Object required and I don't understand why. I've done everything and it still doesn't work. Can you give me some advice on what I could do?
Check your code against my code you are missing something
Loved it. How can I print the code? Thanks❤
the code is located at the end of the video description
I'm getting the following message in a dialog box:
Run-time error '13':
Type mismatch
When I click the debug option, it's taking me to the following line;
qty = Sheet3.Cells(2, 2) {line 7 in Sub recieve()}
Can you help me out with this please?
The only thing I can think of is the value. It is declared as a long, in other words an integer. Are you something with a decimal like 4.2 ?
In that case declare Dim qty as double
Can i link this to work with a barcide scanner?
that is what it is designed for
How Can I get access to this sheet you have created?
I sell my templates that include code for $50 USD. If you email me at easyexcelanswers@gmail.com I can arrange for the purchase.
Is the year end sheet empty?
The original customer just totaled her sales on that page.
so we dont need that sheet right?
@@BarbHendersonconsulting
@@RofoIT there is no real use to have it
@@BarbHendersonconsulting can you revised the code, i want the ingoing to be in a separate sheet than the scan sheet. The scan sheet only display the latest inserted item details.
can you give me this file for free
The code is located in the description of the video for free so you can make your own. I charge for the template.
Hey mam, actually i wanna keep the record of the data entry, it refreshes every time, i enter the data...wht should i do?
There is always a record on the incoming sheet, it is only the inventory that is updating