This is from recording a macro in Excel to insert a new sheet. Couldn't you automate
this for each new sheet you needed to add to your workbook, read in the stuff from each
of your "copy to .." until all of your data was displyed in the workbook?

** insert a new sheet ...
Sheets("Sheet1").Select && starts with the first sheet
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Tom or Something"
Range("A1").Select

** then read in the data, one way or another