Adding contents of multiple files and putting them into one single file

Originally Posted by EsKay!

I have 46 files with identical column headings and rows and I have to summarise the contents of each file into one file. Is there a way to merge these files automatically in one file? What I want is the data from the same cell addresses in each file to be added and put into the same cell addresses of a single new file. I have tried linking the files, but the number of rows and columns run into hundreds.

Can this be done? I will appreciate any help.

Hi,
Here is a similar thread:
http://www.excelforum.com/showthread.php?t=589590

Originally Posted by EsKay!

I will appreciate if you could answer the following questions:

1. What does "InvoiceTest" in Workbooks("InvoiceTest") stand for? Is it the file name?

2. What should I type in = Range ("M3") if the range in each of the worksheets is A1 to HC1150?

Please place this code in a workbook "InvoiceTest"(for this example)that is not in the folder to extract the info,
This example searches a folder called C:\Invoices\ you will have to change it for the folder you require.
Check to make sure the sheet names are correct as well...

Sub GetNewData()
    Dim i As Integer
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
        'Change path to suit
        .LookIn = "C:\Invoices\"
        .FileType = msoFileTypeExcelWorkbooks

        If .Execute > 0 Then    'Workbooks in folder
            For i = 1 To .FoundFiles.Count    'Loop through all
                'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(.FoundFiles(i))


ActiveSheet.Range("A1:HC1150").Copy _
Destination:=Workbooks("InvoiceTest").Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)

                ActiveWorkbook.Close savechanges:=False
            Next i
        End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Thanks. I have made the changes and tried to run the Macro, but it does not return anything in the file InvoiceTest.xls. I do not get any error messages either. So I presume the Macro does run but there is something else that I am doing wrong. The InvoiceTest file is saved in G: Drive and the data files are in H: drive.

This is what I have done:

Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
        'Change path to suit
        .LookIn = "H:\Excel Help\"
        .FileType = msoFileTypeExcelWorkbooks

        If .Execute > 0 Then    'Workbooks in folder
            For i = 1 To .FoundFiles.Count    'Loop through all
                'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(.FoundFiles(i))


ActiveSheet.Range("A1:HC1150").Copy _
Destination:=Workbooks("InvoiceTest").Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)

                ActiveWorkbook.Close savechanges:=False
            Next i
        End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Range("A1:HC1150").Copy

Do you actually have data in Column A?

In plain English....

What this code does is,
Opens a workbook in the folder, copies A1:HC1150
Then it goes to Workbooks("InvoiceTest").Sheets("Sheet1"), finds the last entry in column A moves down 1 row and pastes what has been copied, it then closes the workbook, and opens the next workbook,

if there is no data in column A, you will always be pasting to the same spot, is this the case?