I have multiple workbooks with two sheets in each.

excel1. xls ... has sheets : ABC, ABC-TB
excel2.xls ... has sheets : XYZ, XYZ-TB
excel3.xls ... has sheets : PQR, PQR-TB

I want to create a workbook excel_all.xls with two sheets

- DATA-TB having the data from all the other excel sheets (ABC-TB, XY-TB, PQR-TB etc) in it. This is a simple concat of all the data from the other sheets. The number of rows in each sheet may vary. There are no duplicates across the sheets, number of columns and header rows are same.

- DATA having all the data from other sheets (ABC, XYZ, PQR).

Is there a dynamic way to do this, that is through referencing the other excel files instead of doing a copy function, so that whenever there is a change it is reflected in the excel_all.xls

Would much appreciate help on this.