vlookup between two workbooks in VBA

[Albusaidi]

Hi,

i have tried alot doing vlookup between two workbooks, one is open (book1) and the other
needs to be opened by GetOpenFilename() function let us call it (book2).

(book1) contains sheet1 where i want to do the vlookup
(book2) contains sheet2 where i want to match the id (column 1) and get the names (column2) by vlookup.

here is my code. Notice that the number of rows changes every month so, i need to loop until the last row in sheet1(in book1).


Sub status_first_Step()

Range("C:C").Insert

Set book1 = Sheets("Sheet1")


book2 = Application.GetOpenFilename()
Workbooks.Open book2
Set book2 = ActiveWorkbook

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

ws1.Cells(i, "C").Value = Application.VLookup(book1.Cells(i, 1).Value, book2.Sheets("sheet2").Columns("A:Y"), 2, 0)

Next i


End Sub


Thank You.

 

[Flaiban]

Try this code :

Sub Vlookup()
Dim Fname As Variant
Dim rw As Long, x As Range, ColI As Range, R As Range
Dim extwbk As Workbook, twb As Workbook


Set twb = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select Archive")
If Fname = False Then Exit Sub

Set extwbk = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set x = extwbk.Worksheets("Type_Name_Sheet").Range("A2:Y100000")


With twb.Sheets("Sheet1")
.Range("C2:C" & Cells(Rows.Count, 1).End(xlDown).Row).Clear
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 3) = Application.VLookup(.Cells(rw, 1).Value2, x, 25, False)
Next rw

End With


twb.Sheets("Sheet1").UsedRange.EntireColumn.AutoFit
twb.Sheets("Sheet1").Range("C2:C" & Cells(Rows.Count, 1).End(xlDown).Row).HorizontalAlignment = xlCenter

extwbk.Close savechanges:=False

End Sub