Automate report Generation

**Epscan** · 06-17-2009, 01:08 PM

I have multiple files that I need to get data from.
My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).

The file name is below:

Cell A1 contains this formula ='C:Reports\Year 2009\[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:Reports\Year 2009\[CityWaterReports Feb 2009.xls]PHFlows'!$B$44
and so on.

I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.

Any ideas on how to do this?

**JBeaucaire** · 06-17-2009, 08:53 PM

This should get you started:

Please Login or Register  to view this content.

**ravishankar** · 06-18-2009, 06:27 AM

Hi
Save the attached file in the folder containing reports and run the macro.
It will list file names in col A and value of B44 in col B for all workbooks
for the year specified by the customer.
Ravi

**Epscan** · 06-18-2009, 05:11 PM

JBeaucaire your solution seems to work as I need it.

ravishankar it does work but not quite as needed.

Thanks to you both for the fast reply

**JBeaucaire** · 06-18-2009, 05:20 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)