hi,
below is the part of the code I used
'Vlookup from Lookup_file
Workbooks.Open OpenPath & Lookup_file
Workbooks(Lookup_file).Worksheets(1).Activate
RM_col = ActiveSheet.Rows(1).Find("TALENTPROFESSIONAL", lookat:=xlWhole).Column
PMO_col = ActiveSheet.Rows(1).Find("PRIMARYMKTOFFERING", lookat:=xlWhole).Column
P_Ind_col = ActiveSheet.Rows(1).Find("PRIMARY_INDUSTRY", lookat:=xlWhole).Column
P_sec = ActiveSheet.Rows(1).Find("PRIMARY_SECTOR", lookat:=xlWhole).Column
SubMO_col = ActiveSheet.Rows(1).Find("SUBMARKET_OFFERING1", lookat:=xlWhole).Column
PEP_col = ActiveSheet.Rows(1).Find("PEP", lookat:=xlWhole).Column
PTR_col = ActiveSheet.Rows(1).Find("PTRCHAMPION", lookat:=xlWhole).Column
Strtdt_col = ActiveSheet.Rows(1).Find("STARTDATE", lookat:=xlWhole).Column
Enddt_col = ActiveSheet.Rows(1).Find("ENDDATE", lookat:=xlWhole).Column
Workbooks(temp).Worksheets(1).Activate
MsgBox "Total time took: " & Format((Timer - strt_time) / 60, "#0.00") & " Minutes or " & Format(Timer - strt_time, "#0.00") & " Seconds."
strt_time = Timer
i = 0
For i = 2 To Lastrow
On Error Resume Next
Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), RM_col - 1, False)
Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PMO_col - 1, False)
Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), P_Ind_col - 1, False)
Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), P_sec - 1, False)
Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), SubMO_col - 1, False)
Cells(i, 10) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PEP_col - 1, False)
Cells(i, 11) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), PTR_col - 1, False)
Cells(i, 12) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), Strtdt_col - 1, False)
Cells(i, 13) = Application.WorksheetFunction.VLookup(Cells(i, 1), Workbooks(Lookup_file).Worksheets(1).Columns("B:BB"), Enddt_col - 1, False)
Next i
Workbooks(Lookup_file).Close savechanges:=False
Regards,
Dileep