Thread: Create one Table from Tables

the attached file is an example of the report that is obtained for every equipment. The report is generated by the system with the exact same headers, except that the lenght of the data going down can vary depending on the range of dates requested, since it presents the information on a daily basis.

I want to create one table the contains all the data of the rows. I have include one cell that is the equipment ID and truck operator found in a single cell of each report and the rest of the information is found on a daily basis - of the rows going down section.

I have attached 1 file with 2 tabs (2nd and 3rd tab) emulating the 25 different equipments I have to bring together into one table or list. I have also included how I would like to have the summary look in the first tab as a result. The question is how do I accomplish this?

Thank you,
Juan Peralta

HI

Clarification please. Do you have multiple data workbooks and you want to summarise into a separate workbook, or are they sheets in a workbook that you want to summarise onto one sheet.

If there are 25 individual data workbooks, are they all in the same directory location as the output workbook, and are these the only files in that location?

rylo

The data data workbooks are differents as that is how each Product (equipment) is generated. It generates an individual excel report for each.

That is why I added the excel files. One is how I need it to look "Result" and the other two is and example of the data.

It is located in the same locations. If you can provide me the feedback based on your PC location, then I could change the location in the formula and simply adapt it to where it is located in mine.

Let me know if this explains it better.

Hi

In your main output workbook (test for forum), insert a general module and enter the code.

Sub importdata()
  Dim OutSH As Worksheet
  Set OutSH = ThisWorkbook.Sheets("sheet1")
  OutSH.Rows("2:" & WorksheetFunction.Max(2, OutSH.Cells(Rows.Count, 1).End(xlUp).Row)).ClearContents
  Set fs = CreateObject("scripting.filesystemobject")
  For Each f In fs.getfolder(ThisWorkbook.Path).Files
    If f.Name <> ThisWorkbook.Name Then
      Workbooks.Open (f.Name)
      arr = Array(Range("F6").Value, Range("G6").Value, Range("H6").Value, Range("F9").Value, Range("H9").Value)
      For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row
        outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSH.Cells(outrow, 1).Resize(1, 5).Value = arr
        OutSH.Cells(outrow, "F").Value = Cells(i, "C").Value
        OutSH.Cells(outrow, "G").Value = Cells(i, "D").Value
        OutSH.Cells(outrow, "H").Value = Cells(i, "F").Value - Cells(i, "E").Value
        OutSH.Cells(outrow, "I").Value = Cells(i, "G").Value
        OutSH.Cells(outrow, "J").Value = Cells(i, "H").Value
        OutSH.Cells(outrow, "K").Formula = "=J" & outrow & "/I" & outrow
        OutSH.Cells(outrow, "L").Resize(1, 4).Value = Cells(i, "I").Resize(1, 4).Value
      Next i
      ActiveWorkbook.Close savechanges:=False
    End If
  Next f
End Sub

Save this workbook, and the data files in their own directory with no other files. Then run the code.

HTH

rylo