VBA Match cell value in another workbook and HLOOKUP row data

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
ABCDE
1NameBugs Bunny
2
3
4
5Training DescriptionTraining 1Training 2Training 3Training 4
6Duration0.50.50.32
7RefreshOnce Only (N/A)Once Only (N/A)Once Only (N/A)Once Only (N/A)
8Level NYC/CCCC
Indiv Training
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B8:O8Cell Value="Testing"textNO
B8:O8Cell Value="C"textNO
B8:O8Cell Value="NYC"textNO
B8:O8Cell Value=4textNO
B8:O8Cell Value=3textNO
B8:O8Cell Value=2textNO
B8:O8Cell Value=1textNO
Cells with Data Validation
CellAllowCriteria
B7:O7List='C:\Users\Mick and Emma\Downloads\[Individual competency sheet - structural template.xlsm]Sheet1'!#REF!


Training Matrix -Test.xlsm
ABCDEFGH
2Employee No.NameColourTradeTraining 1Training 2Training 3Training 4
312345Bugs BunnyRedBoilermakerc
412346Daffy DuckBlueBoilermaker3333
Master Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H7:ET602,S2:EQ6,E3:R6Cell Value="N/A"textNO
H7:ET602,S2:EQ6,E3:R6Cell Value="C"textNO
H7:ET602,S2:EQ6,E3:R6Cell Value=2textNO
H7:ET602,S2:EQ6,E3:R6Cell Value=4textNO
H7:ET602,S2:EQ6,E3:R6Cell Value=3textNO
H7:ET602,S2:EQ6,E3:R6Cell Value=1textNO
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hi,
Any suggestions from an awesome individual out there?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,788
Messages
5,626,890
Members
416,208
Latest member
tan21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top