Barcode Scanning - Data from one spreadsheet to another


New Member
Feb 25, 2016
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 :)

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
Upvote 0
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
            If c.Interior.ThemeColor = xlThemeColorAccent6 Then
                Set c = .FindNext(c)
                bFound = True
                c.EntireRow.Interior.ThemeColor = xlThemeColorAccent6
                Set c = .FindNext(c)
                nLastRow = wsScanned.Range("A" & Rows.Count).End(xlUp).Row + 1
                wsScanned.Range("A" & nLastRow).Select
            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
    Range("C6") = "UPC: " & Range("Scan") & " Found:=" & Format(bFound, "True/False")
    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.
Upvote 0
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?
Upvote 0

Forum statistics

Latest member

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