Dear all,

I made a label form by a drop down list via validation function in A1:A12
for the junior (source is in another sheet of the same workbook), once the
user choose a value from the drop down list, I need the excel copy the
current cell to next two columns in the same row.

I also failed in using "Is Notempty" event in similar coding.....


Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1:A12")) Is Nothing Then
With Target
Me.Range.Copy
Me.Range.Offset(0, 1).Paste


End With
End If

Application.EnableEvents = True


Best Regards,

Norika




Thanks to Bob Phillips give me many hints but I still failed to do so. :-(

Hi Norika,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo XIT
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1:A12")) Is Nothing Then
If Not IsEmpty(Target) Then
With Target
.Copy
.Resize(1, 2).Offset(0, 1) = .Value

End With
End If
End If
XIT:
Application.EnableEvents = True
End Sub

---
Regards,
Norman



"norika" wrote in message
...
Dear all,

I made a label form by a drop down list via validation function in A1:A12
for the junior (source is in another sheet of the same workbook), once the
user choose a value from the drop down list, I need the excel copy the
current cell to next two columns in the same row.

I also failed in using "Is Notempty" event in similar coding.....


Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1:A12")) Is Nothing Then
With Target
Me.Range.Copy
Me.Range.Offset(0, 1).Paste


End With
End If

Application.EnableEvents = True


Best Regards,

Norika




Thanks to Bob Phillips give me many hints but I still failed to do so. :-(