Excel Help

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

Hello all! I recently posted a message, but got no
response. I was wondering if anyone could get me started
on this. Any suggestions would be appreciated. I have
several reports that I want to take select information
from (all in Excel format) and put that information into
a temporary file (Excel preferably)and hold it until I am
ready to use it for a mass report? I want the
information update everytime I run one of the reports.

The reason that I am needing this is because I have five
different reports that I run on a monthly basis. I want
to take SELECT information from each of those reports and
put it into one big report. I want the temporary file to
update when I run one of those five reports. Each report
needs to be on a separate sheet. I need the sheet to
update when I run the small report. When this update is
done, I don't want to override the file, I want to
override the sheet. Then, I want to create a macro to
retreive the information from the temporary file.

Are there any suggestions on how to get this started? I
need to somehow create link between the temporary file
and the five reports that I run every month. Any help
would be greatly appreciated. Thanks in advance and
Happy Holidays!

Julie
 
Generating a report could describe many different things that can be done in
Excel. It would be difficult to describe a possible solution with such a
vague description. If you have a macro that generates the report in terms
of a formatted worksheet, then you could have the same macro copy the sheet
to your temp file or copy selected cells to a sheet in your temp file.

Other than that, I think you would need to be more specific.
 
Hi Julie,
Your request is too vague and not possible with just a
few clicks of the mouse and some coding here and there,
consider the following, can you guarantee that the SELECT
data is always in the same place (same number of rows
columns) for each rerun of the report and for each report,
the file names are always the same. Alternative, are all
five reports coming from one system, if so surely the
report provider can create a new report to you
requirement. if your company has and IT department, go an
speak to them and ask them to sort you out an over summary
report, if that not available then I suggest you take a
look at RentACoder.com, for a small fee someone will fill
your requirements.... for a small fee of course. If this
option is also not possible you'll need to be more
specific eg what does you data look like, say for example
the 5 reports are 5 regions that show revenue for
products, if the is a product code you could use a vlookup
function across all five reports, but remember to call
your workbooks none specific eg don't label them Region
North 200312.xls because you'll only have to update each
referrence link every month. Name the 5 reports
accordingly like Region North.xls this way your reporting
set of six files can be updated when ever and at the end
of each month you can store the monthly reports so if
required you can replace the Region North.xls with a
stored Region North 200312.xls and rename is to Region
North.xls. Anyway I sure I've proposed many question but
its best that you speak to your IT department.

regards
KM
 
Hi Tom,

Thanks for your response. I just wanted to pass on some
information on my reports. I have created all of the
report macros for all five reports (with some help now
and then). On each report I have a summary page. From
those summary pages I create graphs to show my figures.
I am needing to pull dollar figures from the summary
pages. On the summary pages, I have categories that are
constant with only the dollar amounts changing. I have
two reports that I manipulate and create summaries for,
and the others I need to grab the figures from the report
itself. One these reports the figures are never in the
same place, but I do have constants that I can use. I am
going to try a few things and see if I can make this as
easy and efficient as I can. I am thinking of creating
summary pages for all five reports and creating a macro
to call up the summary page. I have a bunch of ideas
going through my head, I am just trying to get an idea on
what would be more efficient. I think that I can figure
it out if I work on it long enough. I know that this is
still kind of vague, but in some situations it is hard to
understand unless you can see everything that is being
talked about. Thanks so much for your time and
assistance. I see your name often here. You have been a
big help to alot of people. I hope you know that you are
appreciated. Happy Holidays!

Julie
 
Hi and thanks for your response. I have a lot of ideas,
but I guess that I need to think through all of the
possibilities. I have created all the the macros,
summaries and graphs for all my other reports. This is a
little more complex than my other reports because I am
using so mayny sources. You did bring up some good
points on the file names, positions, and vlookup. I will
be sure follow your suggestions. Thanks for your
assistance and have a great day!

Julie
 
I wouldn't worry about being efficient.

In the code that processes/creates each report, I would open the temporary
workbook and write the appropriate data to a page in the temporary
workbook.. If it takes many hard coded copy and paste range pairings, so be
it. Once they are written you don't have to fuss with them. If there an
opportunity to loop or to process multiple cells at a time, cleary you
should use those. Of course, the macro recorder can often be a help in
such a situation.
 
Back
Top