I have a macro that is working to pull information from another file to
update cells but it is only linked to one file. I want to be able to name
the file in cell A2 but the other information should stay the same as each
file has the same format and sheet names.

Range("c2").Select
ActiveCell.FormulaR1C1 = "='[1776 CLO I Recon 011309 V
18.xls]Acct'!RC[-2]"

C2 is the cell to be updated from file = 1776 CLO I Recon 011309 V 18.xls
sheet Acct moving over 2 column's on that sheet.

I want to put in cell A2 the name of the file = 1776 CLO I Recon 011309 V
18.xls and have my "ActiveCell.Formula" use that so I can change the name in
cell a2 as I move to the next file.

Any help would be appreciated

Range("c2").FormulaR1C1 = "='[1776 CLO I Recon 011309 V 18.xls]Acct'!RC[-2]"
becomes

with activesheet 'I like qualifying my ranges
.Range("c2").FormulaR1C1 _
= "='[" & .range("A2").value & "]Acct'!RC[-2]"
end with

This assumes that the workbook (with the name in A2) is open, too.


Don wrote:

I have a macro that is working to pull information from another file to
update cells but it is only linked to one file. I want to be able to name
the file in cell A2 but the other information should stay the same as each
file has the same format and sheet names.

Range("c2").Select
ActiveCell.FormulaR1C1 = "='[1776 CLO I Recon 011309 V
18.xls]Acct'!RC[-2]"

C2 is the cell to be updated from file = 1776 CLO I Recon 011309 V 18.xls
sheet Acct moving over 2 column's on that sheet.

I want to put in cell A2 the name of the file = 1776 CLO I Recon 011309 V
18.xls and have my "ActiveCell.Formula" use that so I can change the name in
cell a2 as I move to the next file.

Any help would be appreciated

--

Dave Peterson

Thanks Dave, this helps

"Dave Peterson" wrote:

Range("c2").FormulaR1C1 = "='[1776 CLO I Recon 011309 V 18.xls]Acct'!RC[-2]"
becomes

with activesheet 'I like qualifying my ranges
.Range("c2").FormulaR1C1 _
= "='[" & .range("A2").value & "]Acct'!RC[-2]"
end with

This assumes that the workbook (with the name in A2) is open, too.


Don wrote:

I have a macro that is working to pull information from another file to
update cells but it is only linked to one file. I want to be able to name
the file in cell A2 but the other information should stay the same as each
file has the same format and sheet names.

Range("c2").Select
ActiveCell.FormulaR1C1 = "='[1776 CLO I Recon 011309 V
18.xls]Acct'!RC[-2]"

C2 is the cell to be updated from file = 1776 CLO I Recon 011309 V 18.xls
sheet Acct moving over 2 column's on that sheet.

I want to put in cell A2 the name of the file = 1776 CLO I Recon 011309 V
18.xls and have my "ActiveCell.Formula" use that so I can change the name in
cell a2 as I move to the next file.

Any help would be appreciated

--

Dave Peterson