VBA vlookup with Dynamic Filename. PLEASE HELP!!

[NewAtVba]

Hi, I'm new to VBA and I've been stuck on this problem for a few days. I've googled and checked the forums but all the solutions don't seem to work.

I'm trying to get create a macro that references an older file to update a new file that is received daily. For example, i have a master copy called master.xlsx. I receive a new file today and using vlookup i will copy over the last 4 columns to the new file by comparing the IDs(IDs are in column A). These 4 columns are comments. This new file is now the master copy. The macro will be run from the new file.

This is my current code which is very undynamic. i need to change the filename by storing it as a variable or use the indirect method.

Application.ScreenUpdating = False
Dim Bottom As Long
Bottom = Range("A65536").End(xlUp).Row

Range("r2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[Master Copy.xlsx]Sheet1'!$A$1:$t$" & Bottom & ",17,0)"
Selection.AutoFill Destination:=Range("r2:r" & Bottom)

Range("s2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[Master Copy.xlsx]Sheet1'!$A$1:$t$" & Bottom & ",18,0)"
Selection.AutoFill Destination:=Range("s2:s" & Bottom)

Range("t2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[Master Copy.xlsx]Sheet1'!$A$1:$t$" & Bottom & ",19,0)"
Selection.AutoFill Destination:=Range("t2:t" & Bottom)

Range("u2").Select
ActiveCell.Formula = "=VLOOKUP($A2,'[Master Copy.xlsx]Sheet1'!$A$1:$t$" & Bottom & ",20,0)"
Selection.AutoFill Destination:=Range("u2:u" & Bottom)



The line i tried using indirect was
ActiveCell.Formula = "=VLOOKUP($A2,INDIRECT(""x3"")]$A$1:$t$500,17,0)"
and it doesn't work

I'm open to any solutions. PLEASE help. thank you!!

 

[Andrew Poulsom]

Welcome to MrExcel.

If X3 contains Master Copy.xlsx:

ActiveCell.Formula = "=VLOOKUP($A2,INDIRECT(""'[""&X3&""]Sheet1'!$A$1:$T$500""),17,0)"

or:

ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$T$500,17,0)"

Note that the source workbook must be open in both cases.

 

[NewAtVba]

Hi! thanks for replying so quickly!

i cant get it to work if i include the Bottom variable that i am using. Is there something wrong with my syntax?

ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$T$"&Bottom&",17,0)"

 

[Andrew Poulsom]

That's:

ActiveCell.Formula = "=VLOOKUP($A2,'[" & Range("X3").Value & "]Sheet1'!$A$1:$T$" & Bottom & ",17,0)"

 

[NewAtVba]

it works perfectly. THANKS SO MUCH!!