Getting data from one sheet to another on the user input

Question

I have an Excel workbook, and in that workbook I have a number of sheets.
One is called main and the other sheet data which holds all the information.
The data sheet has people's name and assigned numbers in the same cell.

e.g.: A2 would have: 123 Chris Smith

What I am trying to achieve is for a user to start typing the number anywhere in the main sheet that it scans the data sheet and completes the rest of the data.
So a user would type 123 and the macro would then fill out the rest 123 Chris Smith.

It is doable, by searching whenever the WorkSheet_Change event is procced. However, I suspect the performance on this might be quite poor... — Jakob Busk Sørensen
Have you thought about using AuUtoComplete Data validation? here is a link that may help. Not sure if it is what you want, because it allows a single cell to be "completed", and doesn't allow other values to be put in other cells, but you could do that with Lookup functions. — ainwood

Plagon Plagon · Accepted Answer · 2017-07-10T10:44:58

As already mentioned you can use the Worksheet_Change() Event. To Show you how it works, put this code in the main sheet in the editor. Now type any number that matches your data list and click out of the cell, it will be now completed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sht As Worksheet
Dim rng As Range
Dim val As Variant: val = Target.Value

Set sht = Worksheets("Data")

If Not IsError(val) And Not IsArray(val) Then
    If val <> "" Then
        Set rng = sht.Range("A:A").Find(val & "*", LookAt:=xlWhole)
        If Not rng Is Nothing Then
            Application.EnableEvents = False
            Target.Value = rng.Value
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

Getting data from one sheet to another on the user input

1 Answers