Macro Help (Barcode Scanner to Excel Sheet)

[RebelDrummer]

Thanks for adding me - first post, lets hit the ground running

Here is what I am working with.
1.. Barcode / SKU scanner.
2 .. Excel Master Data List (all on one tab/sheet)

Here is what I am able to do now.
Take the scanner, scan a barcode on a product, excel finds that item and highlights the entire row

Here is what I would like to see happen, if possible.
Take the scanner, scan a barcode on a product, excel finds that information and creates a new row on the new tab/sheet with that data

Is this possible? Is creating a Macro the way?
I do appreciate the help in advance

Thanks

 

[rollis13]

Yes, all you have to do now is use the Macro Recoder to copy your highlighted row to the other sheet. Then, once you have the code for the 'copy' part you need to make the macro dynamic so it can 'add' to the other sheet everytime you do a new reading with the scanner.

 

[RebelDrummer]

Yes, all you have to do now is use the Macro Recoder to copy your highlighted row to the other sheet. Then, once you have the code for the 'copy' part you need to make the macro dynamic so it can 'add' to the other sheet everytime you do a new reading with the scanner.

first, thank you -
second, is there a video tutorial anywhere that will show me the basics of how to do this? i understand the macro recorder option via excel. however, I am not familiar with how to capture that code and make it dynamic to perform the second action (which would be adding it to the 2nd sheet/tab)

 

[rollis13]

Get yourself started with this LINK then came back with your macro if you don't manage to make it dynamic (better if in a new thread since it will be a different topic).

 

[RebelDrummer]

Get yourself started with this LINK then came back with your macro if you don't manage to make it dynamic (better if in a new thread since it will be a different topic).

Thanks for the link. I am still struggling unfortunately. Here is the macro. The problem I am running into at the moment is that when I use the shortcut keys, no matter what I just scanned, it will only paste the info from the first data range that was found when recording macro. I know that its because the macro is specific to that cell information, so I guess I am stuck now trying to figure out how to make it to where it will copy / paste over the new scanned data each time.

Sub DataLogging1()
'
' DataLogging1 Macro
'
' Keyboard Shortcut: Ctrl+b
'
Cells.Find(What:="22060725", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
End Sub

 

[rollis13]

This could be your macro with shortcut key. I'm assuming that your barcode read ends up in cell A1 and the copied row is pasted in Sheet1:

Option Explicit
Sub DataLogging1()
    '
    ' DataLogging1 Macro
    '
    ' Keyboard Shortcut: Ctrl+b
    '
    Dim lr     As Long
    Application.ScreenUpdating = False
    On Error GoTo notfound
    Range("A2:A" & Rows.Count).Find(What:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    With Sheets("Sheet1")          '<- change as needed
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        ActiveCell.EntireRow.Copy .Cells(lr, 1)
    End With
    GoTo nextscan
notfound:
    MsgBox "Item not found!"
nextscan:
    With Application
        .CutCopyMode = False
        .EnableEvents = False
        Range("A1").Select
        Range("A1").ClearContents
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

but since your input is from a scanner I would use this other solution which triggers via event Worksheet_Change. This macro has to be pasted in the input sheet's module and I'm using cell A1 as support cell where the input of the scanner goes.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr     As Long
    If Not Target.Address = "$A$1" Then Exit Sub  'if it isn't the cell chosen for input... <-- change cell as needed
    Application.ScreenUpdating = False
    On Error GoTo notfound
    Range("A2:A" & Rows.Count).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    With Sheets("Sheet1")          '<- change as needed
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        ActiveCell.EntireRow.Copy .Cells(lr, 1)
    End With
    MsgBox "Done!"
    GoTo nextscan
notfound:
    MsgBox "Item not found!"
nextscan:
    With Application
        .CutCopyMode = False
        .EnableEvents = False
        Range("A1").Select
        Range("A1").ClearContents
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[RebelDrummer]

This could be your macro with shortcut key. I'm assuming that your barcode read ends up in cell A1 and the copied row is pasted in Sheet1:

Option Explicit
Sub DataLogging1()
    '
    ' DataLogging1 Macro
    '
    ' Keyboard Shortcut: Ctrl+b
    '
    Dim lr     As Long
    Application.ScreenUpdating = False
    On Error GoTo notfound
    Range("A2:A" & Rows.Count).Find(What:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    With Sheets("Sheet1")          '<- change as needed
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        ActiveCell.EntireRow.Copy .Cells(lr, 1)
    End With
    GoTo nextscan
notfound:
    MsgBox "Item not found!"
nextscan:
    With Application
        .CutCopyMode = False
        .EnableEvents = False
        Range("A1").Select
        Range("A1").ClearContents
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

but since your input is from a scanner I would use this other solution which triggers via event Worksheet_Change. This macro has to be pasted in the input sheet's module and I'm using cell A1 as support cell where the input of the scanner goes.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr     As Long
    If Not Target.Address = "$A$1" Then Exit Sub  'if it isn't the cell chosen for input... <-- change cell as needed
    Application.ScreenUpdating = False
    On Error GoTo notfound
    Range("A2:A" & Rows.Count).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    With Sheets("Sheet1")          '<- change as needed
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        ActiveCell.EntireRow.Copy .Cells(lr, 1)
    End With
    MsgBox "Done!"
    GoTo nextscan
notfound:
    MsgBox "Item not found!"
nextscan:
    With Application
        .CutCopyMode = False
        .EnableEvents = False
        Range("A1").Select
        Range("A1").ClearContents
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

The macro for use with scanner is ideal - however, USER ERROR I believe is the issue I am now facing (eyeroll, i know)
Its been a long time since I have had to use let alone create a macro
At this point, I am not sure what the best course of action is to see where I am going wrong
With this 2nd option set up - it is still not populating the scanned information on the 2nd worksheet.
All it does when the bar code is scanned is it finds the cell in which that data is stored.

 

[rollis13]

It's always hard to make suggestions not being able to see the exact layout of your data. A dummy file attached would be appreciated.
Anyway, as per the second option, I think that you haven't pasted the macro in the sheet's module. As said it uses an event macro so it won't work if pasted elsewhere. As a shortcut instead of going to the Editor, you can right-click the input sheet's Tag and then choose View Code.

 

[RebelDrummer]

LoggingMockUp71422.xlsm
	A	B	C	D	E	F	G
1	SKU	Type	Manufacturer	Model Number	Model Description	Depth	Part #
2	22050001	NERF1	AB	1234	HEMI	9	1A2B3C
3	22050002	NERF2	CD	5678	BOLT	300	4D5E6F
4	22050003	NERF3	EF	9876	PUMP	270	7G8HI9
5	22050004	NERF1	AB	1234	HEMI	9	1A2B3C
6	22050005	NERF2	CD	5678	BOLT	300	4D5E6F
7	22050006	NERF3	EF	9876	PUMP	270	7G8HI9
8	22050007	NERF1	AB	1234	HEMI	9	1A2B3C
9	22050008	NERF2	CD	5678	BOLT	300	4D5E6F
10	22050009	NERF3	EF	9876	PUMP	270	7G8HI9
11	22050010	NERF1	AB	1234	HEMI	9	1A2B3C
12	22050011	NERF2	CD	5678	BOLT	300	4D5E6F
13	22050012	NERF3	AB	9876	PUMP	270	7G8HI9
14	22050013	NERF1	CD	1234	HEMI	9	1A2B3C
15	22050014	NERF2	AB	5678	BOLT	300	4D5E6F
16	22050015	NERF3	CD	9876	PUMP	270	7G8HI9
17	22050016	NERF1	AB	1234	HEMI	9	1A2B3C
18	22050017	NERF2	CD	5678	BOLT	300	4D5E6F
19	22050018	NERF3	EF	9876	PUMP	270	7G8HI9
car parts master

 

[RebelDrummer]

LoggingMockUp71422.xlsm
	A	B	C	D	E	F	G
1	SKU	Type	Manufacturer	Model Number	Model Description	Depth	Part #
2	22050001	NERF1	AB	1234	HEMI	9	1A2B3C
3	22050003	NERF3	EF	9876	PUMP	270	7G8HI9
4	22050008	NERF2	CD	5678	BOLT	300	4D5E6F
5	22050010	NERF1	AB	1234	HEMI	9	1A2B3C
6	22050013	NERF1	CD	1234	HEMI	9	1A2B3C
7	22050014	NERF2	AB	5678	BOLT	300	4D5E6F
8	22050015	NERF3	CD	9876	PUMP	270	7G8HI9
9	22050018	NERF3	EF	9876	PUMP	270	7G8HI9
customer 1 order