Vlookup from other Sheet vba

[vikas_newports]

IN VBA , I want to use vlookup from the other sheet within the same workbook but I do not want to use worksheet name (DOVE) in formula
Currently, I am using the below formula and it is working fine

 Range("C2").Formula = "=VLOOKUP(B2,DOVE!$C$14:$C$1048576,1,0)"

 

[EXCEL MAX]

Hello again Vikas,
will you be satisfied with formula in format like picture 1. or some different string instead "xxxx"?

If yes, you can use "Name manager" and define name for range in the sheet "DATA" like picture 3 shows.

If you were try that without sucsess, take care about two things.
First when formula is inserted in the workbook1 data will not apear automaticaly if calculation options is not turned to "Automatic".
And second... you can get full file path in the formula or in the name manager if not both workbooks opened.
I hope this is little bit closer to the resloving to your problem.

 

[EXCEL MAX]

If you want to insert formula with VBA jus use variable defined in the name manager in the VBA code.

ActiveSheet.Range("A1").Formula = "=VLOOKUP(A2,xxxx,1,0)"

 

[vikas_newports]

EXCEL MAX, but it is taking the sheet name and I want to avoid from sheet name

 

[Fluff]

I want to avoid from sheet name

You can't, no matter what you used in VBA the formula in the cell MUST have a sheet name.
The only other option would be to use Indirect, which has been described as Excel's most evil function.

 

[vikas_newports]

Fluff, yes I was thinking about INDIRECT function too can you share a example of usage ?
Meanwhile I tried below code and it is working fine

Range("C2").Formula = "=VLOOKUP(B2,'" & Sheet2.Name & "'!$C$14:$C$1048576,1,FALSE)"

 

[Fluff]

can you share a example of usage

Nope, I've never used it conjunction with VBA & generally avoid it like the plague.