Copy and Paste from many workbooks into one worksheet

Hi There,

I am trying to copy and paste ranges from many workbooks into a seperate worksheet

The workbooks are in a forlder called C:\Invoices

The ranges I would like to copy and paste to the seperate worksheet is

Sheets("Invoice").Range ("M3,D13:H13,E17,M13")

I would like to be able to loop through all the workbooks in the folder, and have the ranges pasted one row then the next row ect.


Is there a way this can be performed?

Here's some code I have found, with some customizing added

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:\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))


                Workbooks("InvoiceTest").Sheets("Sheet1").Range("A3000").End(xlUp).Offset(1, 0) = Range("M3")

                Workbooks("InvoiceTest").Sheets("Sheet1").Range("B3000").End(xlUp).Offset(1, 0) = Range("D13")

                Workbooks("InvoiceTest").Sheets("Sheet1").Range("C3000").End(xlUp).Offset(1, 0) = Range("M13")

                Workbooks("InvoiceTest").Sheets("Sheet1").Range("D3000").End(xlUp).Offset(1, 0) = Range("E17")

                


                'Sheets("Invoice").Range ("M3,D13:H13,E17,M13")
                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

This code originated from this site

This code is available from many sites.

Thanks Dave,
I thought the code was working great, but then noticed the data being transferred does not always match the invoice, such as the invoice # was in column A (M3) from the Invoice sheet but the amount in column E was showing a different amount (M40) from the invoice sheet ( the amount was coming from another invoice), it had worked well with a small sample, but when I used it for the main folder, they don't match

Does anybody know of a better way, to transfer the ranges from the invoices to the summary sheet?

Originally Posted by Paul Morgan

Thanks Dave,
I thought the code was working great, but then noticed the data being transferred does not always match the invoice, such as the invoice # was in column A (M3) from the Invoice sheet but the amount in column E was showing a different amount (M40) from the invoice sheet ( the amount was coming from another invoice), it had worked well with a small sample, but when I used it for the main folder, they don't match

Does anybody know of a better way, to transfer the ranges from the invoices to the summary sheet?

The most obvious suggestion is that....

Some of the files have ranges that are blank, so when they are transferred to the summary sheet, it pastes a blank,
then when the next sheet is looped, the code will paste the range into the next empty cell, since the previous workbook had a range that was blank, the next workbook opened will paste into the next blank cell, therfore escalating into an inacurate entry

Thanks Dave, That is exactly the problem, with lots of blank cells in invoices , as I have been fixing them and running the macro, data in starting to match