Lookup using VBA

[GeJit]

Hi,
I am completely new to VBA.

My problem is I want to Map Employee ID's to the workbook that user selects and opens.
I have created a mapping table which contains Local ID and External ID for Employees.
When the user opens a workbook, the macro should automatically match the External ID from the active workbook to the Mapping table I created, and retrieve the Local ID and paste to the next blank column of the used range. (planning to assign this Macro to a button which will be placed in the Mapping table Worksheet.).

Could someone help, please.

Posting the code I did till now. Don't know , how to go forward.

Sub MapID()
Dim myFile As String
Dim YourFolderPath As Variant
YourFolderPath = "C:\Trial"
myFile = Application.GetOpenFilename
If myFile = "False" Then Exit Sub
'MsgBox myFile
Workbooks.Open Filename:=myFile

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim lastColumn, ValColumn As Long

Set ws = Sheets("Sheet1")


With ws


'lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
lastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
ValColumn = .Cells(2, lastColumn + 1).Select

End With


Application.ScreenUpdating = True

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your mapping table and the sheet from at least one opened workbook. Alternately, you could upload a copies of your mapping table file and at least one opened workbook to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[GeJit]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your mapping table and the sheet from at least one opened workbook. Alternately, you could upload a copies of your mapping table file and at least one opened workbook to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Due to security reasons, I wont be able to open some Websites and also download files.