automating creating reports from based on a list of names

[alex-22]

Hi

I've just got into using macros and i've come across a problem that i've not worked out a solution to yet. Apologies in advance for the length of the question, but its quite complicated to explain:

I have a large workbook which contains a lot of data fields related to lots of individual people and exam results(~3000 pupils). I have two sheets which use lookups to summarise various bits of the data for the whole data set. Each individual is associated with a school name, and I have another two sheets which summarise the same data for the but for only an individual school.

From this I’ve created a macro that automatically generates a file for each school(which contains the four summary sheets) which is triggered when I input the school name to a particular cell in the workbook.

The problem is that this requires me to sit and input into a cell each school name in order to trigger a report being created. This would be ok for one data file, but I have about 30 other similar files with the same type of macros.

I was wondering if it was possible to add code to the macro to read a list of school names and create a report for each one. I’m guessing this requires some kind of loop function, but I’m not really too sure.


Any help would be much appreciated, if you need any more explanation, I’ll try and explain it a bit better

 

[Norie]

What code do you currently have?

Do you have the schools listed somewhere?

How are you actually triggering the code?

 

[alex-22]

The code i currently have does basically the following in sequence:

opens a template file which contains the two summary sheets for all the schools information

cuts and pastes the two sheets for an individual school to the template workbook

saves the workbook with the schools name.


I don't have the actual code because i'm at home at the moment, but its pretty basic stuff


I trigger all this using a worksheet change function in the data file, which detects when i enter a school in a cell and then executes the macro to compile the files. The two summary sheets for the individual schools work using vlookups mainly.


I also have a list of all the schoools, in a worksheet.