vlookup using Dim reference for different spreadsheet

[grapevine]

I have set the following reference for different workbooks at the start of my macro which is working fine as I swop between different workbooks. (the references are commented out as they are in the option explicit area, but I have shown them for information

'Dim ad As Workbook 'address file suffix HDR
'Dim rp As Workbook 'ratio pack suffix PKS
'Dim sd As Workbook 'main data file DTL

'Call WorkbookName
'Dim od As String
    od = InputBox(Prompt:="Enter the Order Number", _
          Title:="Order Number", Default:="eg JN00104")

        If od = "eg JN00104" Or _
           od = vbNullString Then

           Exit Sub
        Else
        End If

ad = od & "." & "HDR"
rp = od & "." & "PKS"
md = od & "." & "DTL"

I have moved between workbooks using the variations of the following code

Windows(md).Activate
Workbooks("JaneNormanMacros.xls").Worksheets("Main Range IO").Range("A1:w24").Copy
Workbooks(ad).Worksheets("sheet1").Range("A1").PasteSpecial Paste:=xlAll

However I am now trying to do a vlookup from one spreadsheet to another and cannot get the reference to work. I normally use the following format when i know the workbook name

=VLOOKUP(RC[-1],'[lookupfiles.xls]LLoyd TopMan'!burtoneuro,2,FALSE)

but I have tried adapting the code to look at the workbook now referenced as rp but I am getting error 1004 so I know it cannot see the workbook at all. I have tried putting brackets in various places but to no avail and have included below I couple that I have tried.
attempt 1

Range("B16").FormulaR1C1 = "=VLOOKUP(R[5]C[1],'[workbooks(rp)]Formatted File' !ratiopack,5,FALSE)"

attempt 2

Range("B16").FormulaR1C1 = "=VLOOKUP(R[5]C[1],'[rp]Formatted File' !ratiopack,5,FALSE)"

If anyone can point me in the right direction I would be most grateful

 

[Norie]

If you wanted to include the workbook name in the formula you would use rp.Name.

Range("B16").FormulaR1C1 = "=VLOOKUP(R[5]C[1],'[" & rp.Name & "']Formatted File'!ratiopack,5,FALSE)"

 

[Andrew Poulsom]

Assuming that rp contains the name of an open workbook, try:

Range("B16").FormulaR1C1 = "=VLOOKUP(R[5]C[1],'[" & rp & "]'!ratiopack,5,FALSE)"

By the way you have declared your variables as workbook but you are assigning them a string. Given that you mention Option Explicit I'm surprised that you don't get a compile error

 

[grapevine]

Thank you once again for you help, once I put inthe sheet name it worked a treat.

You were also correct about the Dim as Worksheet causing an error - it has been previously I had changed workbook to String in the option explicit area but had forgotten that I had not changed in where I had it originally. Rest assured I have it Dimmed as a String now, but it did cause some grief a couple of days ago.

Once again, many thanks, your help as always is excellent