Referencing another workbook in a VLookUp
 

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.Add ress(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").Cur rentRegion.Address(external:=True, ReferenceStyle:=xlR1C1)"

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

Referencing another workbook in a VLookUp
 

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

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

Highlystrung wrote:

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.Add ress(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").Cur rentRegion.Address(external:=True, ReferenceStyle:=xlR1C1)"

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

--

Dave Peterson

Referencing another workbook in a VLookUp
 

many thanks,
--
thanks, Neil


"Highlystrung" wrote:

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.Add ress(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").Cur rentRegion.Address(external:=True, ReferenceStyle:=xlR1C1)"

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