Passing variable in VLookup string in VBA Code- I get an error

[rv02]

Hi All

I would appreciate any help on my problem below

I want to pass a variable in VLookup function in VBA and keep getting and error.

I have the following

Please Login or Register  to view this content.

What I want for the code above is to replace HW655 WK 5 2016.xlsx] with variable Sheetname so that whatever value Sheetname has it will look at the right file for the vlookup. Sheet name has value of cell t2 in my spreadhseet which will change depending on user input

But I cant seem to be getting this right as I am not sure how to do this , every time I try it has Sheetname as string in the name and not the value of sheet name
any help is appreciate it

[Richard Buttrey]

Hi,

This is confusing. HW655...etc is a WORKBOOK name not a sheet name. Please clarify.

In addition please upload the workbook which has the code and also the HW655...workbook.

[rv02]

Yes it's is a workbook which changes its name based on cell value. The sheet is called summary which is always come same. In the code I want thecworkbook name which is an external workbook to be replace by Sheet name and there's is always the same I.e thecsheet name and the look-up range . The workbook to too large to send sorry

[rv02]

sorry for the confusion , to make it clearer the formula I want to use is as follows
"=VLOOKUP(RC[-1],'[HW655 WK 5 2016.xlsx]SUMMARY SHEET'!C1:C57,5,0)" but I need to replace HW655 WK 5 2016.xlsx by the variable Sheetname (the name of this variable might be confusing but I can change it, it is the name of the workbook). Sheetname is the name of an external workbook which takes its value from a cell in the worksheet based on the user input. I just do nt know how to ut the variable Sheetname in the vlookup formula above instead of HW655 WK 5 2016.xlsx. The sheet in the workbook is called Summary and the lookup range is always the same.

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.