Populating various columns from different workbooks into centralized table.

[JayGatsby]

Hello! I have few csv files (all data in separate columns),e.g file1,file2,file3, file4 and I’d like to combine those in one table in excel placed within separate sheet with static headings. The files have a different structure (i.e. the columns containing relevant info differ/are in different order) and need to be pasted without headings into a table of consistent format (e.g. where column “name” would be fitted in e.g column B2). The location of column “name” would differ per file but that can be adjusted in the code itself.

If possible, I would like to do this without having to open the CSVs, so basically the table within the main sheet would get populated with the data from each (closed) csv based on given path/location. Also, once file 1 is done populating, the file 2 should be populated below without headers (different source columns per file into same destination column below one another). The size of files will vary so it would be good to have the range set as dynamic but the column A of each file defines the number of rows that should be copy/pasted.



Example of the table:



File1 (csv):

Sport	Nationality	Name	Surname	City	Class	Country	Position
Football	Turkey	Kate	Black	Newcastle	54	Turkey	midfielder
Rugby	Australia	Bill	White	Rome	11	Australia	striker

File2 (csv):

First name	Second name	Activity	Origin	Group	Location
Joe	Newton	Hockey	Georgia	22	Georgia
Jack	Smith	Tennis	Japan	34	Japan

Desired result in main sheet:

Action	Nationality	Rating	Name	Surname
Football	Turkey	54	Kate	Black
Rugby	Australia	11	Bill	White
Hockey	Georgia	22	Joe	Newton
Tennis	Japan	34	Jack	Smith