Barcode Scanning - Data from one spreadsheet to another

[Allysa]

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

 

[Neil_Murray]

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]

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

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]

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?