[Solved] Auto complete data on entering ID No

[Anniemo]

I have a workbook, consisting of a sheet for each day of the week and a database which contains all information relating to contractors i.e. ID No. Name, Company, Role etc. Is it possible to create a script or macro, which will when the ID number is entered on a weekday sheet, will auto complete the remaining data for that person from the database sheet? Per the example below, ideally if i enter 123456 in ID No column in the Weekday Sheet, the name etc will autocomplete.

Any help would be much appreciated. I have considered lookups/formulas etc, but the chances are these would end up being deleted/copy pasted/corrupted so wanted some thing reasonably simple and preferably unseen.

Thank you

Database Sheet
ID NO.	Forename	Surname	COMPANY	POSITION
123456	Garry	Bloggs	Acme Inc	Supervisor
234567	Gerry	Blogee	Speedy Inc	Minion
Weekday Sheet
ID No.	FORENAME	SURNAME	COMPANY	POSITION

 

[shamimarita]

Hello Anniemo,

To run the code automatically each time you enter a value in the ID column, you can use an event-driven macro in Excel. Specifically, you can use the Worksheet_Change event to trigger the macro whenever a change is made in the ID column.

Open the Weekday Sheet then copy and paste the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the changed cell is in the ID column (Column A)
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        ' Run the autofill code for the changed cell
        Call AutoFillDetails(Target)
    End If
End Sub

Sub AutoFillDetails(cell As Range)
    Dim db As Worksheet
    Dim ID As String
    Dim found As Range
    
    ' Set the database worksheet
    Set db = ThisWorkbook.Sheets("Database Sheet")
    
    ' Get the ID from the changed cell
    ID = cell.Value
    If ID <> "" Then
        ' Find the ID in the database
        Set found = db.Range("A:A").Find(ID, LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then
            ' Copy the details to the weekday sheet
            cell.Offset(0, 1).Value = found.Offset(0, 1).Value ' Forename
            cell.Offset(0, 2).Value = found.Offset(0, 2).Value ' Surname
            cell.Offset(0, 3).Value = found.Offset(0, 3).Value ' Company
            cell.Offset(0, 4).Value = found.Offset(0, 4).Value ' Position
        End If
    End If
End Sub

The Worksheet_Change event triggers whenever a change is made in the worksheet. It checks if the change occurred in column A (the ID column).
If the change occurs in the ID column, it calls the AutoFillDetails subroutine.
The AutoFillDetails subroutine searches for the entered ID in the Database Sheet and fills in the corresponding details in the adjacent columns.

Download the Excel File: