Private Sub Worksheet_Change(ByVal Target As Range)
Dim lookupValue As Range
Dim lookupRange As Range
Dim sourceWB As Workbook
Dim sourceSheet As Worksheet
' Check if the changed cell is in the ID column (A column)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
' Open the Source Workbook if not already open
On Error Resume Next
Set sourceWB = Workbooks("Sources.xlsx")
On Error GoTo 0
If sourceWB Is Nothing Then
Set sourceWB = Workbooks.Open("C:\Users\Mohsin King\Desktop\Sources.xlsx") ' Adjust the path
End If
' Set the sheet and range where your data is stored
Set sourceSheet = sourceWB.Sheets("DataSheet") ' Adjust the sheet name here
Set lookupRange = sourceSheet.Range("A2:F100") ' Adjust the data range here
' Perform the lookup for the entire row based on the ID entered
If Not IsEmpty(Target.Value) Then
' Fill details based on the code number
Target.Offset(0, 1).Value = Application.VLookup(Target.Value, lookupRange, 2, False) ' Name
Target.Offset(0, 2).Value = Application.VLookup(Target.Value, lookupRange, 3, False) ' Address
Target.Offset(0, 3).Value = Application.VLookup(Target.Value, lookupRange, 4, False) ' Phone
Target.Offset(0, 4).Value = Application.VLookup(Target.Value, lookupRange, 5, False) ' Email
Target.Offset(0, 5).Value = Application.VLookup(Target.Value, lookupRange, 6, False) ' Age
End If
End If
End Sub
