|I need to know how to copy the same specific range of data from multiple individual Excel files to one single Excel file.|
For example, the data range of A28:J32 needs to be imported from files Data1, Data2, Data3, etc. into file Total1.
Individual copying is not an option due to number of Data files. Linking Total1 to the data files, locking the cell references in each cell and then copying helps, but still leaves the business of updating links for each file imported, and it does not iteratively update the references back to the files from where the data is linked.
That is, in file Total1, the formulae within cells A7, A13, A19, A25, A31, etc. all read ='C:\Datapath\[Data1.xls]Sheet1'!$A$28, even though the data from A13 is from file Data2, A19 is from Data 3, A25 is from Data 4, A31 is from Data5, etc.
A macro would be ideal, but need method for it to iteratively go through my data files.