Automatically Generate Reports from Excel Template

[sm1308]

Hello All,
I apologize if a solution has been posted already on the site, I have not been able to find it yet!
I would appreciate any help on the following:

I am looking to automate report generation in excel. I have a workbook with a worksheet that is the "report" which pulls in data (from a data sheet in the same workbook) based on the entry (unique key) in cell a1 in report sheet (the vlookups populate the report each time an entry is made in cell a1).

For each of the possible values for cell a1 (there are 200) I would like to generate a report and have this saved in a specified directory as excel (or pdf). For example let's say i'm generating report cards for a class of 200 and I have the raw data in a file and want to automate the generation of a "report" for each student. Each file will be distributed independently.

I do have a list of the cell values that should be used to generate the reports.
For the time being I am simply changing the value in the cell manually and copying --> paste special values into a new workbook.

Does anyone have any ideas?

Thanks in advance for your help.

[gemsera]

Hi, what format is your existing report in? excel?

I am assuming you have workbook 1:
Sheet 1: Data
Sheet 2: Report?

And you are wanting to export sheet 2 to PDF and save to a location?

Or do you have a seperate report altogether that you populate manually with excel figures on sheet 2?

[sm1308]

Hello,

The document is all in excel,

you are right in that there is a sheet that is report, and a sheet that is data,
I want the report sheet to pull data based on a key (i have a list) and generate a seperate report for each key and output these as either pdf (prefferred) or excel.


Thanks!