Transfer data into an external excel database sheet, based on unique ID match

Question

Good day, I have little knowledge with excel VBA variables and have been struggling for days to make this one work. I had assigned "Workbook2.xlsm" as a central database file. It contains all individual employee IDs in column A(starting from column A2, A1 is a table header). Through an input section within another "Workbook1.xlsm" I am trying to trigger with a VBA to open Workbook 2/Sheet1, match the individual ID entered in cell A2 in Workbook 1 with the list of IDs in column A in Workbook 2, copy the type of training, start and end date from Workbook 1 next to the corresponding ID in Worksheet 2, then close and save Worksheet 2.

Data entry Worksheet 1

Worksheet 2 Database to search ID and paste assignment, on and off from Worksheet 1

Thank you in advance for the ideas.

Answer 1

I've created similar workbook which basically uses one sheet to update another sheet. code below is based on one workbook. you can modify for the two workbooks by adding the workbook name before the sheet, turn off screen updating, open workbook2, assign the values, save workbook2, turn on screen updating.

i recommend creating a table so that the range will be automatically dynamic as new employees are added "TblData". you can also do this with a named range, but in my experience, it is a little cumbersome. With a little more coding, you can have option to add employee. Name your button "AssignTrainBtn". This code would be located in the ManageAssignment sheet object assigned to the click action of AssignTrainBtn

Private Sub AssignTrainBtn_Click()
Dim formSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer
Dim IDCol As Range
    
Set formSheet = Sheets("ManageAssignments")
Set dataSheet = Sheets("TempAssignments")
Set IDCol = dataSheet.Range("TblData[ID]")
     
ID = Range("a2")
    
    If ID <> vbNullString Then
    
    Set Rng = IDCol.Find(what:=ID, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        MatchCase:=False)
        
    If Not Rng Is Nothing Then
    
        nextRow = Rng.Row
    
        Else
        MsgBox ("employee not found")
Exit Sub
    
    End If
        
    dataSheet.Cells(nextRow, 2).Value = formSheet.Range("b2").Value
    dataSheet.Cells(nextRow, 3).Value = formSheet.Range("c2").Value
    dataSheet.Cells(nextRow, 4).Value = formSheet.Range("d2").Value
    
    End If
End Sub