Shortcut or code for copying from multiple workbooks into one

[reginafilangie]

sample_summary.xlssample_department.xls

Above I have two sample files. I have 13 departments exactly like "sample_department" but with different answers (same cell locations, same tab names). I want to take those 13 department files and consolidate them into "sample_summary". "sample_summary" will have tabs assorted by the order of questions asked, as shown. The questions in the department file do not need to be copied over, just the answers in their respected locations. Is there a faster way to do this? I'm on a deadline so any shortcut helps! I'm willing to attach anything else if this is unclear.

Thank you!

[Spencer101]

Hi ReginaFilangie (nice Friends ref there!)

How often are you going to have to do this?
If one off then shouldn't be too much of a problem to set up one set of answers use find and replace to change file names in each subsequent row.

If you're going to have to do this often, you may wish to go down a different route...