VBA: Loop through files in a folder and perform vlookup to pull in data to a master file

[CamVilla]

Hi, can someone help me, how can I adapt the vlookup to loop through the diferent workbooks in the selected folder. Also is there a faster way to do the vlookup?

Sub vlookup()

Dim MyFolder As String, MyFile As String

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
On Error Resume Next
MyFolder = .SelectedItems(1)
Err.Clear
On Error GoTo 0
End With

If MyFolder = "" Then End

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False

Workbooks.Open Filename:="C.....xlsm"
Sheets("BASE").Select
Range("G13").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC1,[MyFile]Sheet1Hoja1!RC[-6]:R1000C[136]R13:R65536,+COLUMN([MyFile]Sheet1!R[1]C),FALSE)"
Range("G13").Select
Selection.AutoFill Destination:=Range("G13:JK243"), Type:=xlFillDefault
Range("G13:JK243").Select


Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir

Loop

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationManual

End Sub