dynamically changing a formula for vlookup

[Danny54]

is there a way to dynamically change the workbook name in a Formula vba statement? I'm looping thru multiple workbooks based on the filename found in the directory and each passthru of the macro would have to reflect the workbook just opened.

first pass workbook name is "Servers with Access.xlsx" Sheet name No Dup
second pass workbook name is "Workstation with Access.xlsx" Sheet name Dups
ect.... until the whole directory is processed

Thanks



Range("J4").Formula = "=IFERROR(VLOOKUP(I4,'[Servers with Access.xlsx]No dup'!$C:$C,1,False),"" "")"
Range("J4").AutoFill Destination:=Range("J4:J" & lastrow)

 

[Joe4]

You can capture the name of the workbook as you open it like this:

Dim wsName As String
wsName = ActiveWorkbook.Name

and then you can use that in your formula like this:

[COLOR=#333333]Range("J4").Formula = "=IFERROR(VLOOKUP(I4,'[" & wsName & "]No dup'!$C:$C,1,False),"" "")"[/COLOR]

 

[Danny54]

sweet, thanks so much. I never thought about capturing the workbook name as I opened it.

Worked like a Champ.

Have a Wonderful Day!

 

[Joe4]

You are welcome.
Glad I was able to help!