Cut an entire cell, based on specific text, and paste to a new cell in the same row

Question

I have a six-column table in excel that I am using with a barcode scanner. We are scanning custom QR codes that display text into each cell. The scan starts with scanning the employee's QR code on their badge followed by the specific item(s) that are being checked out or in. The table's range is B2:G2. Column B is labeled Employee #, Columns C-F are labeled ITEM #1-4 and the 6th column and final column is labeled IN/OUT. The scanner we use tabs through each cell after the custom text is scanned and entered in each cell.

I would like to cut an entire cell, based on the specific text IN or OUT, and paste it to a new cell. For example, if in columns D-F the barcode scanner enters IN or OUT, I want to cut the IN or OUT value from the cell and paste it into column G, plus automatically tab to the following table's row and cell in column B after the paste occurs. Column B and column C should never have text containing IN or OUT.

Is this possible? I found a similar question dealing with multiple workbooks and cutting entire rows instead of cells, so I figured this might be achievable as well.

I experimented with different methods of VBA, macros, and formulas in excel, but none of them could give us what we wanted. Our goal is to limit the number of scans needed. If a person comes in and scans out all 4 items, then there will be no need to cut and paste IN or OUT. After 4 items are scanned the scanner will scan the QR code with the text IN or OUT into column G and automatically tab to column B. However, a majority of our people only check out 1, 2, or 3 items and we want to eliminate any additional "Blank" scans or having to go over to the computer to press tab or click in a new cell. The columns where this will occur will always be B-G but will need to continue for each row (B2:G2, B3:G3, B4:G4) We would be using at least 600 rows for the approximately 300 people we have.

My experience writing code is limited to say the least. Any code recommendations or suggestions would be greatly appreciated.

Answer 1

try

Private Sub Worksheet_Change(ByVal Target As Range)
    'check if only one cell is changed
    If Target.CountLarge > 1 Then Exit Sub
    'check if target is in columns D, E, or F
    If Intersect(Target, Range("D:F")) Is Nothing Then Exit Sub
    'check if target value is capital "IN" or "OUT"
    If Target.Value <> "IN" And Target.Value <> "OUT" Then Exit Sub
    
    Application.EnableEvents = False             'prevent triggering another change event
    Target.Cut Destination:=Cells(Target.Row, 7) 'cut and paste in column G
    Cells(Target.Row + 1, 2).Activate            'move active cell to next row, column B
    Application.EnableEvents = True              'reenable events
End Sub