Referencing another workbook in a VLookUp

[Highlystrung]

I have written some code as follows:

ActiveCell.FormulaR1C1 = "=vlookup(rc[-1]," & strTableRange & ",4,false)"

I populate strTableRange with the reference to the sheet4 as follows:

strTableRange =
Worksheets("sheet4").Range("A1").CurrentRegion.Address(external:=True,
ReferenceStyle:=xlR1C1)

I now want to move the sheet 4 into a separate file in the same folder and
have tried to reference it thus:

strTableRange =
"(Issues.xls)!worksheets("Sheet4").Range("A1").CurrentRegion.Address(external:=True, ReferenceStyle:=xlR1C1)"

but it doesn't work. Can someone point out where my syntax is wrong? Many
thanks

 

[Dave Peterson]

strTableRange = workbooks("Issues.xls").worksheets("Sheet4") _
.Range("A1").CurrentRegion.Address(external:=True, _
ReferenceStyle:=xlR1C1)

The Issues.xls workbook has to be open, too.

I have written some code as follows:

ActiveCell.FormulaR1C1 = "=vlookup(rc[-1]," & strTableRange & ",4,false)"

I populate strTableRange with the reference to the sheet4 as follows:

strTableRange =
Worksheets("sheet4").Range("A1").CurrentRegion.Address(external:=True,
ReferenceStyle:=xlR1C1)

I now want to move the sheet 4 into a separate file in the same folder and
have tried to reference it thus:

strTableRange =
"(Issues.xls)!worksheets("Sheet4").Range("A1").CurrentRegion.Address(external:=True, ReferenceStyle:=xlR1C1)"

but it doesn't work. Can someone point out where my syntax is wrong? Many
thanks

 

[Highlystrung]

many thanks,