Extracting data from multiple .xls files in folder and creating new workbook

**haedyr** · 12-01-2013, 06:42 PM

I created a macro to extract data from a bunch of excel files into one new workbook. When I ran the code, it didn't extract any files, but did create a blank summary file with headers. I defined the variables so that I could extract them from the workbooks. As you can see, I only need data from some columns, and I want to capture all rows of data. Am I overthinking this? (I haven't used VBA in 6 years and I was never that great at it to begin with)

Please Login or Register  to view this content.

Thanks for your help!

**TMS** · 12-01-2013, 07:28 PM

Looks like the main problem is that you loop through all the files and, when you've finished doing that, you create the new workbook and put in headings.

Suggest you create the new workbook first and assign it to a variable.
Then, for each of the workbooks, determine the last row of data.
Copy all columns between the first row of data and the last row (established above)
Paste into the new workbook.
When you've finished looping, delete the columns you don't need.
Top and tail with headers and footers.
Done.

Regards, TMS