Copy Data from First Sheet of Multiple XLS Files in 1 Folder and Copy to 1 New XLSX Sheet

  • I have spent 10 hours trying and trying my hand at this code. It seems like a repetitive question after scouring the web yet any code I came across gave me some kind of issue, even with hours of tinkering. That being said - SOS


    >I have hundreds of workbooks in a folder on my X drive (X:\Purchasing.330\ERP\GRC\AACG\MSIT2 Incident Detail Reports)

    >They all only have one worksheet, titled "Incident Report"

    >They have a various number of lines, all typically maxing out at line 10,027.

    >Each workbooks' data starts at line A28.

    >Each column has data to column AA (last column with data. Column AB and onwards is empty)

    I just want to copy all of this data into my original open sheet (Sheet1) without any line gaps in the data. Just an excel 2010 worksheet. Should the workbook I'm copying into be open or close? I'm good with anything.


    The header of the first document should be copied but the header from the remaining documents should be left alone. The header starts on line 1 and ends on line 27. The remaining data starts at line 28.


    As an aside, these files all downloaded as as an Excel HTTP Page? So every time you try to open one of the files, you get the error that I have attached below. But if you click "Yes", it opens as expected. Is that an underlying issue?


    Thank you SO much in advance. This community is incredible.

  • Here's a file that may work better. This one only has 3k lines but all the others typically tap out at 10k, like I said above. Posting a screenshot so you can see how the file should open and look!



    All of the hundreds of files will start with the same name "Access Incident Details Extract Report_"

  • I'm having trouble opening the Report file. I get an error message that the file is in a different format than that specified by the file extension. The file extension is "xls" which is an older Excel extension. Please check your file and perhaps save it with an "xlsx" extension the same as your Book1 file and try attaching it again.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    Exactly. When I download all of the files, they download as XLS. So I was trying to figure out how to make the macro run on XLS files. Anyways, I learned how to batch convert them all to XLSX today so it's fine! I can convert them first and then run the macro. Attached is a XLSX copy of the data.

  • First make sure that all your source files have an "xlsx" extension. Place this macro in a regular module in the Book1 file and run it from there.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.