Copy multiple cells from several work books to one workbook

See if this thread can get you in the right direction
http://www.excelforum.com/showthread.php?t=589590

Great link I am almost there.....it runs fine but the final result is it only posts the data from the final invoice. I tested this macro with five invoices (142, 143, 144, 145, 146) Only the data from the 146 invoice workbook went onto the "Income Statement Sheet.xls".....something I did to kill the loop?


Here is the VBA:

Sub GetInvoiceData()
    Dim i As Integer
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Range("A2:F400").ClearContents 'delete sheet to suite

    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:\Documents and Settings\Lyds\Desktop\Test"
        .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))


                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("b2").End(xlUp).Offset(1, 0) = Range("A16")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("c2").End(xlUp).Offset(1, 0) = Range("G15")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("d2").End(xlUp).Offset(1, 0) = Range("H36")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("e2").End(xlUp).Offset(1, 0) = Range("H37")
                
                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("f2").End(xlUp).Offset(1, 0) = Range("H38")

                
                'Sheets("Invoice").Range ("A16,G15,H36,H37,H38")
                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 Dave....This websiet is Great!

Originally Posted by Blur1973

Great link I am almost there.....it runs fine but the final result is it only posts the data from the final invoice. I tested this macro with five invoices (142, 143, 144, 145, 146) Only the data from the 146 invoice workbook went onto the "Income Statement Sheet.xls".....something I did to kill the loop?


Here is the VBA:

Sub GetInvoiceData()
    Dim i As Integer
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Range("A2:F400").ClearContents 'delete sheet to suite

    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:\Documents and Settings\Lyds\Desktop\Test"
        .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))


                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("b2").End(xlUp).Offset(1, 0) = Range("A16")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("c2").End(xlUp).Offset(1, 0) = Range("G15")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("d2").End(xlUp).Offset(1, 0) = Range("H36")

                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("e2").End(xlUp).Offset(1, 0) = Range("H37")
                
                Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("f2").End(xlUp).Offset(1, 0) = Range("H38")

                
                'Sheets("Invoice").Range ("A16,G15,H36,H37,H38")
                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 Dave....This websiet is Great!

Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("b2").End(xlUp).Offset(1, 0) = Range("A16")

you need to go farther down then xlup