new guy with a conceptual question

J

Jay

hi,

i need to create a weekly report pulling data from a
series of 6 daily reports which each exist in their
own .xls document. i want to create a macro to do this
automatically

what's the best way for me to access the data? should i
copy it into hidden tabs in my weekly report, or call it
directly from the existing files.

i would like to be able to enter the week ending date
(let's say "i"), and then have excel automatically gather
the week's data (i-0,1,2,3,4,5)which will populate the
formulas i create on the weekly page.

i'm just beginning my vba foray and would greatly
appreciate any help. thanks
 
K

Keith R

Jay-
Will the source data files always be directly accessable from their "active"
location (e.g. on a server or your personal machine) or will copies of those
workbooks be sent to you as updates, for example by email?

If they are in a live location, and someone has it open when you are ready
to run your report, do you want to grab the data as it was when the file was
last saved, or wait until the next time the file is closed (assuming the
active user remembers to close it)?

Are the daily reports dependent on other processes (other files, systems,
etc) or could you potentially keep them permanently in your workbook, and
only expose the weekly report when you need to run it?

:)
Keith
 
G

Guest

Thanks for the quick response Keith,

The files will always be in their active location.
However,I think it's better to import the data to the
weekly report and hide those tabs.

i recorded a macro which involved opening each file (by
pointing to it), selecting all the data in the appropriate
tab, copying it, pasting it into my "active" tab, then
returning to the daily file and closing it. I repeated
the process 5 times. It works perfectly

the source files are named by date ("DFR.040223.xls) where
the first two digits are the year, the middle two digits
are the month and the last two digits the day. i would
like to designate the month/day the week ends, then have
excel loop through the days including the week end day and
the five days previous to populate the six (hidden) tabs
in my weekly sheet.

possible?

thanks
 
Top