CODEWITHSUNDEEP
vbaexcelvlookup
Mar
Mar

Reputation: 1129

VBA VLOOKUP from different excel file with different names

Hi I do a vlookup in other excel spreadsheet in a different excel file. Now my problem is the name changes all the time.

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[TEST2.xlsx]Sheet1!C1:C8,8,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range("A" & rows.Count).End(xlUp).Row)
Range("B2:B" & Range("A" & rows.Count).End(xlUp).Row).Select

So basically it looks in TEST2.xlsx for the data it needs and than it puts in a list in my original excel file. But the TEST2 gonna change all the time, everytime I'm gonna use this macro

How Can I do this?

Upvotes: 1

Views: 4632

Answers (1)

quantum285
quantum285

Reputation: 1032

Not entirely sure what you require but if I'm right you could do something like this.
It's worth noting that it is not necessary to select cells first but rather you can reference them directly.

Sub lookupSub(dataFile)
Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],[" & dataFile & "]Sheet1!C1:C8,8,FALSE)"
Range("B2").AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Select
End Sub


You can then call this as follows:

Call lookupSub("TEST2.xlsx")


Or if it works from within a loop you could do something like this: 

For i = 1 To 10
    Call lookupSub("TEST" & i & ".xlsx")
Next i

Upvotes: 1

Related Questions