Macro to consolidate data from many files

Greetings,

I need a macro that gathers information from 3 different workbooks and consolidates it on one master file

Each of the three workbook has 6 different sheets, and same named and layout sheets are there in master workbook file as well

Basically what i need is sum of the 3 files respective sheets data in to master file same named sheet in the same cells

Eg

Workbook 1 has sheet 1, sheet 2, sheet 3 , sheet 4, sheet 5, sheet 6
Workbook 2 has sheet 1, sheet 2, sheet 3 , sheet 4, sheet 5, sheet 6
Workbook 3 has sheet 1, sheet 2, sheet 3 , sheet 4, sheet 5, sheet 6

Master has sheet 1, sheet 2, sheet 3 , sheet 4, sheet 5, sheet 6

what i want is in master file sheet 1( sum of same cells from sheet 1 from workbook 1, sheet 1 from workbook 2 and sheet 1 from workbook 3),sheet 2( sum of same cells from sheet 2 from workbook 1, sheet 2 from workbook 2 and sheet 2 from workbook 3),sheet 3( sum of same cells from sheet 3 from workbook 1, sheet 3 from workbook 2 and sheet 3 from workbook 3),sheet 4( sum of same cells from sheet 4 from workbook 1, sheet 4 from workbook 2 and sheet 4 from workbook 3),sheet 5( sum of same cells from sheet 5 from workbook 1, sheet 5 from workbook 2 and sheet 5 from workbook 3)



I will be eternally grateful if anyone can help me crack this!

Can anyone help me out please?

You would require a nested loop of 3 loops, 1. for Master file - for each sheet run a loop 2. For all workbooks. 3. each sheet in selected workbook.

when it is innermost loop, use the loop(sheet) value of Master making equal to sheet of other workbooks.

Otherwise simply open all workbooks, write down formula in each sheet "=W1.sheet1!A1+W2.sheet1!A1+W2.sheet1!A1" (Something like that) and drag till your desired range. do the same for all sheets.

You would require a nested loop of 3 loops, 1. for Master file - for each sheet run a loop 2. For all workbooks. 3. each sheet in selected workbook.

when it is innermost loop, use the loop(sheet) value of Master making equal to sheet of other workbooks.

Otherwise simply open all workbooks, write down formula in each sheet "=W1.sheet1!A1+W2.sheet1!A1+W2.sheet1!A1" (Something like that) and drag till your desired range. do the same for all sheets.

@ Marecco, hat doent give me desired result, it gives result from only one sheet and doesnt even add up in master file

@ Ayush, can u give me a sample structure of the macro,?

when i use normal ="=W1.sheet1!A1+W2.sheet1!A1+W2.sheet1!A1" my file goes beyond 35mb, so i want to use macro to copy and later paste as values to keep file size low as possible

@ Ayush, can u give me a sample structure of the macro,?

when i use normal ="=W1.sheet1!A1+W2.sheet1!A1+W2.sheet1!A1" my file goes beyond 35mb, so i want to use macro to copy and later paste as values to keep file size low as possible

Please Login or Register  to view this content.

does this structure suit my requirement, it gets get run time error whenever i run it

range to be copied is i13:pf1162, all three files saved in one folder say D:new\segments, the master file is also present in the same folder

Can anyone help on this please?

Can anyone throw some light on this please????