VBA Match cell value in another workbook and HLOOKUP row data

[EmmaFos]

Hi All
I'm quite new at VBA and really need some advice please on how to transfer data. I have Individual's Training Record sheet that I need to update into a master file whenever a new benchmark is met.
I've written a successful code to copy and transpose the data over to a sheet in the Master file, but then I need to look up the person's name from cell "A2" of the transposed Indiv Sheet, look for the name in column "B" of the Master sheet and insert the training level as per the row heading. I imagine a HLOOKUP type function would do this and then paste values? This file has over 100 training options that could be updated per individual and this is above my skill level so would really appreciate some help!

Thanks

Sub OpenWorkbookTrnsps()

'Open a workbook

 

  'Open method requires full file path to be referenced.

  Workbooks.Open "C:\Users\Documents\Training Matrix Test.xlsm"

 

  'Unhide data worksheet

  Worksheets("Indiv Training").Visible = True

 
'Clear_Existing_Data_Before_Paste()


Dim wsCopy As Worksheet

Dim wsDest As Worksheet

Dim lCopyLastRow As Long

Dim lDestLastRow As Long

 

  Set wsCopy = Workbooks("Individual Record.xlsm").Worksheets("Training")

  Set wsDest = Workbooks("Training Matrix Test.xlsm").Worksheets("Indiv Training")

        

    '1. Find last used row in the copy range based on data in column A

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

     

    '2. Find first blank row in the destination range based on data in column A

    'Offset property moves down 1 row

    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

   

    '3. Clear contents of existing data range

    wsDest.Range("A1:H" & lDestLastRow).ClearContents

 

    '4. Copy & Paste Data

    wsCopy.Range("A4:H127").Copy

    wsDest.Range("A5").PasteSpecial Transpose:=True

 

'Hide data worksheet

  Worksheets("Indiv Training").Visible = False

 

'Close a workbook

  Workbooks("Training Matrix Test.xlsm.xlsm").Close SaveChanges:=True

 

  'Close method has additional parameters

  'Workbooks.Close(SaveChanges, Filename, RouteWorkbook)


End Sub

Training Matrix -Test.xlsm
	A	B	C	D	E
1	Name	Bugs Bunny
2
3
4
5	Training Description	Training 1	Training 2	Training 3	Training 4
6	Duration	0.5	0.5	0.3	2
7	Refresh	Once Only (N/A)	Once Only (N/A)	Once Only (N/A)	Once Only (N/A)
8	Level NYC/C	C		C	C
Indiv Training

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:O8	Cell Value	="Testing"	text	NO
B8:O8	Cell Value	="C"	text	NO
B8:O8	Cell Value	="NYC"	text	NO
B8:O8	Cell Value	=4	text	NO
B8:O8	Cell Value	=3	text	NO
B8:O8	Cell Value	=2	text	NO
B8:O8	Cell Value	=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B7:O7	List	='C:\Users\Mick and Emma\Downloads\[Individual competency sheet - structural template.xlsm]Sheet1'!#REF!

Training Matrix -Test.xlsm
	A	B	C	D	E	F	G	H
2	Employee No.	Name	Colour	Trade	Training 1	Training 2	Training 3	Training 4
3	12345	Bugs Bunny	Red	Boilermaker	c
4	12346	Daffy Duck	Blue	Boilermaker	3	3	3	3
Master Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H7:ET602,S2:EQ6,E3:R6	Cell Value	="N/A"	text	NO
H7:ET602,S2:EQ6,E3:R6	Cell Value	="C"	text	NO
H7:ET602,S2:EQ6,E3:R6	Cell Value	=2	text	NO
H7:ET602,S2:EQ6,E3:R6	Cell Value	=4	text	NO
H7:ET602,S2:EQ6,E3:R6	Cell Value	=3	text	NO
H7:ET602,S2:EQ6,E3:R6	Cell Value	=1	text	NO

 

[EmmaFos]

Hi,
Any suggestions from an awesome individual out there?

Thanks