I apologize if this is a redundant question, but I wasn't sure how to search for this specific issue.

Lets say I have a master workbook named "Dallas". This workbook consists of a summary worksheet labelled "Dallas" and maybe 20 worksheets with numbers for names, representing a database desk that each employee accesses.

The process involves using a database program to create numerous .XLS files, one for each desk, named "CALLCYC-CFXX.xls", where "XX" represents the desk number. So "CALLCYC-CF20.xls" is the data for Desk 20. I then open each .XLS file from the Temp folder, copy the entire contents of Sheet1, paste into the corresponding sheet in "Dallas" workbook, then rinse and repeat for each file created. This becomes troublesome with Chicago and its 180 desks.

I then select all sheets, and type "=Average(M1:M65535)" into cell M65536, which the summary Dallas sheet targets and we get the data we need. Save the file with the date at the end of the filename and move on with my life. Its a three hour process, an hour and a half of which is copy-pasting in Excel.

Is there a way to automate the copy-paste process? So it will take an open workbook with the filename "CALLCYC-CFXX.xls", read the numbers where "XX" are, copy the data in Sheet1, paste that data in the corresponding sheet of "Dallas", then close the "CALLCYC" file and move to the next open "CALLCYC" workbook?

I think I attached this thing properly. I deleted data in columns A through L for privacy concerns.
DALLAS 05-03-12mod.xls