Transfer data from one file to another in Excel automatically

Question

I have two Excel files, the first file contains about 5500 cells, the second file is empty and I want to transfer the contents of each sheet in the first file to the cells of the second file.

Clearly, I want to transfer the contents of the cells in the first sheet (J2: O2) to glcm11 in the second file , and the contents of cells in the first sheet (J11:O11) to glcm12, and the contents of cells in the first sheet (J20:O20) to glcm13,and the contents of cells in the first sheet (J29:O29) to glcm14.

Then , I want to transfer the contents of the cells in the second sheet (J2: O2) to glcm21 in the second file, and the contents of cells in the second sheet (J11:O11) to glcm22 , and so on . How can I do this automatically without the manual method because the number of sheets in the file is too large .

I have applied The code below , but i did not get the required data format in Excel file . The code transfer only first value (value in "J" cell ) from the source file ,and copied it to all excel cells in the destination file (from B to G).

the first file the second file The resulting file

Answer 1

EDIT 5.9.2019 15:36

I FINALY found a solution in one step. Just put the formula into the cell B2 (edit the name of the file in the formula) press Enter and then copy the B2 cell and fill in the table.

!!! You must have the same header as in the source file. I can see some differences: Entropy:entropy; disimilarity:Disimilaritys

=INDEX(INDIRECT("[NAME OF SOURCE FILE.xlsx]Sheet"&LEFT(RIGHT($A2;2+LEN($A2)-LEN($A$2));1+LEN($A2)-LEN($A$2))&"!$J$1:$O$"&RIGHT($A2;1)*9-7);RIGHT($A2;1)*9-7;MATCH(B$1;INDIRECT("[NAME OF SOURCE FILE.xlsx]Sheet"&LEFT(RIGHT($A2;2+LEN($A2)-LEN($A$2));1+LEN($A2)-LEN($A$2))&"!$J$1:$O$1");0))