Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 16
If Target.Offset(3, 0).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row - 2
End If
Cells(lRow + 3, lCol).Value = Target.Value
Target.ClearContents
End Select
End If
exitHandler:
Application.EnableEvents = True
End Sub
I suppose?
Bookmarks