Copying data from one spreadsheet to another quickly
 

I am trying to take a couple of hundred excel spreadsheets each having their
own format and input them into a template with a single format. This
template will have a set of fields that I want to always have. Some of the
original files have these fields, but not all. How can I quickly grab
columns of data from my original files and drop them into my template without
having to individually cut and paste these columns?
Different ideas I have had is to create a formula at the top of a column
within my template that would look like:
='[alumni_collegeofarchitecture1-31-06.xls]all'!$B$2:$B$5718
But then I have to copy this formula all the way down to box B5718 on my
template. Is there an easier way?
Thanks, JA