Dazzawm
Well-known Member
- Joined
- Jan 24, 2011
- Messages
- 3,816
- Office Version
- 365
- Platform
- Windows
I have the code below that when I enter a number into a cell in column AE on sheet 1 it changes to what is the corresponding number in column A on sheet 2 to what is next to it in column B on sheet 2. It works fine but if I select multiple cells at once and paste nothing happens, is there a slight tweak that will overcome this please?
Code:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AE:AE")) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Application.Match(Target.Value, Sheets("Sheet2").Columns("A"), 0)) Then
Target.Value = Application.VLookup(Target, Sheets("Sheet2").Range("A:B"), 2, 0)
End If
Application.EnableEvents = True
End If
End Sub