VBA to Import Specific Range of data from multiple excel files into 1 excel file

Hi there,
I am trying to create a macro which will allow me to import data from multiple excel sheets into one excel file but imported as separate worksheets within the file.

For instance, I have 5 separate excel files all from different folder where the data provider will input the data on a monthly basis. I simply want to build a macro that will import the range of data (i do not want to import the headers just a specific range of data from these 5 separate files into 1 file but 5 tabs in the file).

Summary Table:

I want to automatically update the Main File by copying specific range from

Worksheet 'Raw Data1' from File 1 and
Worksheet 'Raw Data2' from File 2 and
Worksheet 'Raw Data3' from File 3 and
Worksheet 'Raw Data4' from File 4 and
Worksheet 'Raw Data5' from File 5

into

Worksheet 'Raw Data1' in Main File
Worksheet 'Raw Data2' in Main File
Worksheet 'Raw Data3' in Main File
Worksheet 'Raw Data4' in Main File
Worksheet 'Raw Data5' in Main File

Hope this makes sense. Any help will be appreciated!!

Originally Posted by Richard Buttrey

Do the separate file vary in size or layout. If not why not simply link your Main File sheet cells to the relevant cells in the supporting files? You then avoid the need to import.

Hi Richard thanks for your prompt response. The files have the same layout but not all have same column name and varies in # of columns.

I thought about creating external reference in the main file but as the file will be shared with peers I prefer to import via VBA code instead. Personally i have bad experiences with external reference (especially if you are sharing the file with peers) the chance of reference being invalid or broken is there.