Vlookup in VBA using variable as datasheet

[batchjb69]

Hi,

I'm trying to create the below lookup in VBA using a variable as the datasheet to lookup to. I get an error of "Object does not support this Property or Method" any ideas why

Sub lookuptoanothersheet()
Dim wbkmain As Workbook
Dim wbkdatasheet As Workbook

Set wbkmain = ThisWorkbook

Set wbkdatasheet = Workbooks.Open("C:\Users\james\Documents\VBA\example.xlsx")

wbkmain.Worksheets(1).Range("b1").Formula = "vlookup(A1,[" & wbkdatasheet & "]Table1!A1:C200,2,0)"


    
End Sub

[scottiex]

Dim wbkdatasheet As Workbook

why do you call it a sheet when its a workbook? that is confusing.

at a guess you need to make it the string(name) in the formula not a sheet object

also you need and = sign

wbkmain.Worksheets(1).Range("b1").Formula = "=vlookup(A1,[" & wbkdatasheet.name & "]Table1!A1:C200,2,0)"

[Richard Buttrey]

In the line that sets the formula use

wbkdatasheet.Name

and don't forget the = sign

[batchjb69]

Perfect Thanks Richard

[batchjb69]

good point re sheet

adding the equals sign doesn't make a difference. I still get the same error