Referencing another workbook in a VLookUp

  • Thread starter Thread starter Highlystrung
  • Start date Start date
H

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
 
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
Click to expand...
 
Back
Top