Macro to pull data from another file - update name of file

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

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.

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
Click to expand...
 
Thanks Dave, this helps

Dave Peterson said:
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.

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