I receive about 50 spreadsheets each week by email. I open the attachment,
verify the data and want to run a macro that will copy just one cell of data
from the worksheet and put the value ($) into a seperate (always the same
name) workbook that has column headings (Row A) of the date of the end of
each week. Column A has the number of the office sending the data. Each
worksheet I receive has both the week ending date (which corresponds to the
date in Row A) and the office number (which corresponds to the office number
in Column A). The cell containing the data is always constant.
Example, the file I receive and open is from Office 10, (cell A1=10), and
dated 05/29/05 (cell A2=05/29/05). The total sales is $3,000 (cell A3=3000).
I want to post the info in cell A3, to workbook named summary.xls and place
it in the correct cell for the matching week and office number. In the
summary.xls workbook, let's say that week 05/29/05 is in cell F1, and Office
10 is in cell A11. The target cell would be the intersection of F1 and A11 or
F11. How do I write a macro that reads the date, office variables in the
original workbook, then validates both variables in the new workbook, and
them determines the row and column and resolves to an individual cell to
write $3,000 to F11? This would be a great help!