Home > Back-end >  Automatically move from column D back to A after data input
Automatically move from column D back to A after data input

Time:10-26

I need to make it so when a user enters data (scans a barcode) into column D it will automatically move them back to column A so they can start over.

CodePudding user response:

You can use event Worksheet_Change

  Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 4 And ActiveSheet.Cells(Target.Row, 4).Value <> "" Then
            ActiveSheet.Cells(Target.Row, 1).Value = ActiveSheet.Cells(Target.Row, 4).Value
            ActiveSheet.Cells(Target.Row, 4).Value = ""
        End If
    End Sub

CodePudding user response:

A Worksheet Change: Select Cell in Next Row

  • This code needs to be copied to the sheet module, e.g. Sheet1, of the worksheet where it should be applied, not in a standard module, e.g. Module1.
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const SOURCE_FIRST_CELL_ADDRESS As String = "D2"
    Const DESTINATION_COLUMN_STRING As String = "A"
    
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    With Me.Range(SOURCE_FIRST_CELL_ADDRESS)
        Dim srg As Range: Set srg = .Resize(Me.Rows.Count - .Row   1)
        If Intersect(srg, Target) Is Nothing Then Exit Sub
    End With
    
    Me.Cells(Target.Row   1, DESTINATION_COLUMN_STRING).Select

End Sub
  • Related