Vlookup with External Sheet in VBA

[vikas_newports]

Hello
I hope all of you are doing well
I want to use vlookup from external file = Data and Worksheet = Support
as I already know how to use vlookup but I do not want to use workbook name and Worksheet name in my formula
this is the existing formula. Please help me to Write formula without using Workbook name and Worksheet Name in Formula

Range("B2").Formula = "=VLOOKUP(A2,'[DATA.xlsm]Support'!$A$2:$B$500,1,0)"

 

[vikas_newports]

Excel Max , before implementation I want to add that I want to lookup from my Macro workbook to Simple Excel Workbook so the 1st workbook is Macro enabled WB and 2nd is the Simple which will be opened on 2nd number

 

[vikas_newports]

RoryA, Getting Run-Time error 1004:
Application-Defined or object Defined error
both workbooks are open

 

[EXCEL MAX]

I have Office 2007 and this works for me.
I don't know what you mean "Simple" . i Suppose you mean "Macro Disabled" .
Before run Workbook1 change this...
[DATA.xlsm] in the vlookup function to the ... for example [DATA.xlsx]

 

[vikas_newports]

EXCEL MAX, I need to admit that i am confused about how to use it , mean all these code should placed in my Macro workbook and insert a new module

 

[RoryA]

RoryA, Getting Run-Time error 1004:
Application-Defined or object Defined error
both workbooks are open

You didn’t answer my question about the value of the workbook name variable.

 

[EXCEL MAX]

I understand that you are confused, because this is only example to show that is possible.
It's only answer to your first post.
There is no universal solutions.
We can't resolve problem and give you solution if we don't have enought information.
Solution allways depends what you trying to do and many other things.
Sometimes the solution is not absolutly that what you suppose to be.
We are here to help each other and making a way to the solution for you and everyone who have simmilar problem,
but we only need more precise information.

 

[EXCEL MAX]

Solution 2.
Try to remove single quotes from formula and write like this.
Range("B2").Formula = "=VLOOKUP(A2,[" & otherWorkbookName & "]Support!$A$2:$B$500,1,0)"

 

[vikas_newports]

RoryA, I am not getting your point what is the value of the workbook name variable.
I used the variable as you defined in code
further I tried it on separated sample files too

 

[vikas_newports]

EXCEL MAX, the main perpose of this question I want to avoid file names in formula

 

[RoryA]

When you get the error and press Debug, if you move the cursor over the otherWorkbookName variable it should show you its current value in a tooltip that pops up. Is it the correct workbook, and does that workbook have a Support worksheet?