Excel barcoding solution

[hotlava]

Hi folks, new to these forums, hoping that someone can help me out.

I am trying to create a spreadsheet that stores readings from a bar code scanner along with the date and time of the scan. I have managed to get it working on a simple level but need to take it on to the next level.
Currently, the cursor starts in cell A1, the barcode is scanned, the date and time are entered automatically into cell A2 and the cursor moves to cell B2 awaiting the next barcode scan.

This is my current code entered in the 'view code' area of worksheet1.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub

ActiveCell.Offset(-1, 1).Select

If Target.Cells.Count = 1 And Target.Column = 1 Then

Target.Offset(0, 1) = Now

End If
ActiveCell.Offset(1, -1).Select

End Sub


What I would like to happen is that the cursor constantly sits in cell A1 on worksheet1, the barcode is scanned and the relevant data be placed in cell A1 and A2 on worksheet2. The next scan causes the new data to be placed in cell B1 and B2 on worksheet 2. No data needs to be stored on worksheet1, I'd like it cleared after each scan and the cursor to remain in cell A1. Worksheet1 will be used solely as a 'data entry screen', locked down appropriately so users can't get in too much of a mess.

I'm new to Excel coding (or any form thereof!) and am a little stuck as to how to go about this.

Any pointers would be much appreciated.

thanks.

 

[shg]

Welcome to the board.

Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Sheet2").Cells(Rows.Count, "").End(xlUp).Offset(1)
        Target.Copy .Cells
        .Offset(, 1).Value = Date
    End With
    Me.UsedRange.ClearContents
    Application.GoTo Me.Range("A1")
End Sub

 

[hotlava]

thanks for the response, when I try this I get an 'Run-time error 13: type mismatch'

Debug button takes me here with the highlight on the line in red:

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet2").Cells(Rows.Count, "").End(xlUp).Offset(1)
Target.Copy .Cells
.Offset(, 1).Value = Date
End With
Me.UsedRange.ClearContents
Application.GoTo Me.Range("A1")
End Sub

I'm using Excel 2007 if that's important.

 

[shg]

Oops:

With Worksheets("Sheet2").Cells(Rows.Count, "[COLOR=red]A[/COLOR]").End(xlUp).Offset(1)

 

[hotlava]

Ok, once I enter some information in cell A1 I get:

Run-time error '28': Out of stack space

If I click Debug I end up with this line highlighted

Application.GoTo Me.Range("A1")

If I click in the debugger I get this:

Run-time error '-2147417848 (80010108)': Method 'ClearContents of object 'Range' failed.

It does seem to be entering some data in Sheet2

 

[hotlava]

seems to be working pretty well in Excel 2003 now, thanks shg!

 

[mitekcomputers]

What if you wanted to take this one step further, and scan in the qty for each part number... leave everything just like it is BUT add a Qty for each part number as it is scanned and drop that is Column C on the 2nd sheet.

In other words scan Part Number -> Qty, Part Number -> Qty, Part Number -> Qty

I am literally doing the same thing as you, BUT I am dealing with about 25 part numbers total, BUT each part number is received in large quantities such as 500 pieces at a time.

Thanks.

Mike