Barcode Scanning - Data from one spreadsheet to another

Allysa

New Member
Joined
Feb 25, 2016
Messages
3
Hello everyone! I'm new here, but this looks like an EXCELLENT source of information. Thanks in advance for all the help.

I searched and searched, but I have a somewhat unique issue - and I'm an Excel NEWB.

Here's my situation:

I have a .xlsb file that is full of items (literally tens of thousands. This document has UPC numbers, retail amounts, etc... as well as around 8 other columns.

I have a large (physical) inventory that I just purchased from an burned down Target. Target provided me with the .xlsb file when I purchased the stock.

What I would like to do is use USB barcode scanners to locate the rows in my original .xlsb file, and store them into a separate .xlsb file. I want it to be streamlined so I don't have to physically cut and paste each one, because it would take ages.

I have searched locally for people to help me with this (even offering consulting fees), but no one seems to know. If anyone can solve this, I would be most grateful; and even offer a monetary reward/compensation. I'm on a deadline, and I'm stressing out about it!

Thank you so much for taking the time to read this.

Allysa Cordon :)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Neil_Murray

New Member
Joined
Feb 18, 2016
Messages
17
Hi,
the thing to understand about bar code scanners is they basically work the same way as keyboards.
They 'type' in what they see, then process the return code. This is exactly the same as typing in the human readable part of the barcode and hitting enter. You can see shop assistants doing this every day when the barcode has been spoiled.
Every barcode scanner I've used has been set up by default to process return codes (hit enter automatically), but if it doesn't, check the instructions of your scanner. Build your sheet so that all the cells except one are locked and protected from selection. This ensures that when typing (scanning) into the last remaining cell, the cursor stays in that cell after pressing enter. Write your code to do the business after the cell is updated, in the Worksheet_Change event. The finished sheet should work exactly the same way using both methods, typing the code in and using the scanner.
 

Neil_Murray

New Member
Joined
Feb 18, 2016
Messages
17
I've put something together for you to use as an example. PM me if you think you may need it.

Create three sheets, name them 'Scanner', 'Target' and 'Scanned'.
Paste your lookup sheet into 'Target'

Change Range("C4") on the 'Scanner' to the named range 'Scan'.
Lock all cells on the sheet 'Scanner' except range("Scan")
Protect the sheet so that protected cells cannot be selected.
Paste this code is in the 'Scanner' sheet's module

Code:
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngTarget As Range, c As Range, bFound As Boolean, nLastRow As Long, firstaddress As String
    Dim wsScanned As Worksheet
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.Protect UserInterFaceOnly:=True
    Set rngTarget = Sheets("Target").Range("A1").CurrentRegion
    Set wsScanned = Sheets("Scanned")
    With rngTarget
    Set c = .Find(Range("Scan"), LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            If c.Interior.ThemeColor = xlThemeColorAccent6 Then
                Set c = .FindNext(c)
            Else
                bFound = True
                c.EntireRow.Interior.ThemeColor = xlThemeColorAccent6
                Set c = .FindNext(c)
                nLastRow = wsScanned.Range("A" & Rows.Count).End(xlUp).Row + 1
                c.EntireRow.Copy
                wsScanned.Activate
                wsScanned.Range("A" & nLastRow).Select
                ActiveSheet.Paste
            End If
        Loop While Not c Is Nothing And c.Address <> firstaddress And bFound = False
    End If
    If Not bFound Then
        nLastRow = wsScanned.Range("A" & Rows.Count).End(xlUp).Row + 1
        wsScanned.Range("A" & nLastRow) = Range("Scan")
        wsScanned.Range("B" & nLastRow) = "Scanned UPC not found in Target list"
        wsScanned.Range("B" & nLastRow).Interior.ThemeColor = xlThemeColorAccent3
    End If
End With
    Sheets("Scanner").Activate
    Range("C6") = "UPC: " & Range("Scan") & " Found:=" & Format(bFound, "True/False")
    Range("Scan").ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This assumes that each row in 'Target' represents a single SKU. if there is a qty column, then the code would need re-working.
 

Allysa

New Member
Joined
Feb 25, 2016
Messages
3
Hello again!

Most of the items do have a quantity. I'm sorry to sound like a total newbie, but how do you change the range? Are you referring to changing it under the Name Manager in formulas?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top