Do you run code against the report (and that's all you do with it) or do you
run code against the daily report, and shove the results into a master data
workbook that stores your output across multiple days? Will other workers
need to be able to run the code when you are on vacation or sick?
As a general rule, when I am aggregating data over time, I store the code in
the workbook where the aggregate data is stored.
If I just need to see a one-time result (and not store the results in a
centralized workbook), and if I'm generally the only one who will need to run
the code, I save it in my "personal" workbook.
In Excel2003, if you press alt-F11 to open the VBA window, then press Ctrl-R
to make sure the "Project-VBA Project" sub-window is visible, you will see an
item called "VBAProject(Personal.xls). I keep all sorts of code snippets in
there that I use on multiple different workbooks so I don't have to add the
code to each of those workbooks. In Excel 2007 I don't believe that the
personal.xls is shown in the "project" window by default, and there is
probably some cool key combination to show it, but I'm so rarely in 2007 I
haven't looked for it yet- my workaround is to record a macro and tell it to
store it in the personal workbook (I just select one cell to create the
macro), then personal.xls will show in the 2007 VBA project window.
When placing code in the personal workbook, be sure to maintain good coding
practices and fully reference your target workbook, e.g. something like
"activeworkbook.activeworksheet.range("A1").value instead of just
range("A1").value
HTH,
Keith