need help populating a table using a macro

[MikeNYC]

Click on the attachment for details.

[colofnature]

You don't really need a macro for this, a formula will do: assuming that your table in each file M.xls and t.xls are in columns A:B, and that row 1 iconsists of headings as in the tables in your doc, and that the table oin M.xls is on a sheet called Sheet1, put this formula in B1 in t.xls and copy it down column B for each fund code:

=IF(ISERROR(VLOOKUP(A2,[M.xls]Sheet1!$A:$A,1,0)),0,VLOOKUP(A2,[M.xls]Sheet1!$A:$B,2,0))

You can then use Copy/Paste Special Values to replace the formula with the $ values.


HTH
Col

[MikeNYC]

Thanks, but like I said earlier that diagram is a very, very simplified version of what I actually have to work with. I have to populate about 45 (t) templates from one (M) master workbook. Each (t) template has about 450 rows per worksheet and about 9 worksheets per template. A macro would be much better because the other cells in the table have formulas which use the transferred value from B1 and do calculations with it. It would be an incredible pain in the *** to try to find a mistake with a formula like that in the cell - not to mention to copy and paste it so many times because there are spaces between rows which should be blank (might as well enter the numbers manually) - with a formula there's too much room for error and it would be too hard to find a mistake.

If you got any other ideas it would help me out a lot.

-Mike

[colofnature]

I'd usually use formulae as they update in real-time so I don't need to remeber to run a macro when the data changes, but fair enough.

Same assumptions as before: tables are on Sheet1 cols A:B of the various workbooks. Put this code in the "M" workbook:

Please Login or Register  to view this content.

then save all the "t" files in one place - make sure there are no other Excel files in that location or they may get messed up - and change the value of strPath to that folder. The macro will open each file in strPath and put in the fund value in each cell in column B where column A isn't empty.

Col