Need Help on How to Auto Paste Value(s)?

[jim100361]

I already have some VBA in the spreadsheet so I don't want to mess it up and I am afraid
to try some results I've found on the web elsewhere that might mess it up, which is why I'm
posting my specific issue.

The existing spreadsheet is designed to do the following:

We have a mixed box of product, so the spreadsheet is used to sort the product from
the box by removing each piece and scanning the barcode of the product (spreadsheet is
manufacturer specific).

Anyways, the barcode is 23 Characters, within the barcode itself lies the Part Number
(PN) of this particular mfr.

The scan is then broken down utilizing 2 adjacent cells to break it down to show the
PN:

Column B uses =LEFT(A2,8) to show only the first 8 characters of the scan
Column C uses =RIGHT(B2,5) to show only the last 5 digits of the previous result (the PN).

Within the spreadsheet both columns B & C forumla's are repeated as shown above all the
way through to row 5001 (to ensure the sheet is long enough for many items).

Next there is a VBA code that automatically sorts the scanned barcodes in column A so
that if multiple scans result in the same PN in column C, then they would
all be grouped together.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom


    End If
End Sub

All this works fine except I want to take it one step further and have an additional
column which has the actual value pasted of all of column C's results in Column D
(D2:D5001).

This way the user can utilize Column D to copy and paste from it into a program that
will search our database for that number so we can either add quantities to our
DBs inventory and/or add a new item and then input the quantities to our inventory.

[mehmetcik]

range(("D1:D5001").value = range(("C1:C5001").value

[jim100361]

I appreciate the effort, but the code you provided conflicts with the one I posted (the first line where it reads "Private Sub... ...Range)). I did however find another one that works, and this is what I now have:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
          
    Range("D2:D5001").Copy
    Range("B2").PasteSpecial Paste:=xlPasteValues
    End If
End Sub

Though the above does what I want, there seems to be an unforeseen side effect. When I was testing it out, I scanned some barcodes and after the scan it would go to the next column. I changed the layout somewhat so that all the functions being performed were in the third and fourth columns, but the final pasted value is actually in the second column just to make it easier for other users. I also hid the third and fourth columns so it would be less confusing to newer users.

To check myself in regards to the issue I am having with it going into the second column, I rescanned the bar code for the scanner itself to ensure it was using a carriage return and not a tab after the scan, but it still goes to the second column. I even tried another method I found of protecting the columns so no changes would occur, but that isn't working either.

Is there some way I can get this to be more user friendly whereas the user only needs to scan each barcode, the next active cell to scan in becomes the below cell, the adjacent cells in the next column indicate the resulting value and all the while the sheet auto sorts?