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

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
 
D

Dave Peterson

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...
 
D

Don

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell data entering slow! 0
Adding data to MS Access from csv file 0
VBA Coding Help for Beginner 0
Link data from Another cell 1
Macro 3
minus signs, or dashes in a formula or hyperlink 3
Protected word template - vb/macro to update footer 7
Macro to Consolidate Data from Various Excel Files 1

Top