copy the drop down list value and paste to next column

[norika]

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. :-(

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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. :-(
>
>
>
>
>