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?

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

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.

@ 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

Sub cons_data()

Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
Dim sourceSheet As Worksheet
Dim CurrentFileName As String
Dim myPath As String
Dim sname As String

Application.ScreenUpdating = False

myPath = "D:\trial"                    'The folder containing the files to be combined

CurrentFileName = Dir(myPath & "\*.xls")    'Finds the name of the first file of type .xls in the current directory

Set Master = ThisWorkbook                   'Create a workbook for the recap report

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    
    For Each sourceSheet In sourceBook.Worksheets
      Set sourceData = sourceSheet
    
      With sourceData
            With sourceSheet
          sname = Left(CurrentFileName, Len(CurrentFileName) - 4) & Format(.Index, "000")
          .Copy After:=Master.Worksheets(Master.Worksheets.Count)
          ActiveSheet.Name = sname
      End With
       
          Master.Worksheets.Add(After:=Master.Worksheets(Master.Worksheets.Count)).Name = sname
          .Cells.Copy Master.Worksheets(sname).Range("A1")
      End With
       
    Next
    
    sourceBook.Close
  
    'Calling DIR w/o argument finds the next xls file within the current directory.
    CurrentFileName = Dir()

Loop While CurrentFileName <> ""

Application.ScreenUpdating = True

End Sub

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????