Consolidate data from multiple files in a directory by matching column headings

I need help modifying an existing report with macros. I created this file a year ago combining different macros I've found online so it may not be pretty. The source data format has changed so now I'm stumped.

Here's a little background info in the consolidation report file attached. It has three operations:
1) FILE DIRECTORY would scan a user prompted location and list out all the source data files in the LIST tab
2) Once the files are listed the CONSOLIDATE DATA button would open each file from the list one by one and copy paste into the ROLLUP tab using the ranges from Columns D and E and finding the last blank row to append the new data.
3) CLEAR DATA is self explanatory

I've attached examples of the source data which is no longer consistent with one another. The NAME/TYPE/EQUIP/PROJECT columns will remain the same but the date range headings will vary for each file so I can't rely on using Column D and E as a reference anymore. I need the macro to somehow match the column headings in order to append the data. I'd also like to be to be able to combine just all the Planned data into one Summary tab, Actuals in another, and Delta in a third sheet. The VBA codes are saved under a single module.

Hi

Try this I think it works!

I`ve made a few assumptions, that you wanted to line up the data based on first month, months will always be consecutive, that you didn`t need subtotals just a grand total for each sheet, the first 4 column headings on the data sheets are NAME, TYPE, EQUIP, PROJECT but they were NAME, TYPE, HOURS, PROJECT on the consolidation sheet so I changed the consolidation sheets.

I`ve tidied up the getdata sub quite a bit, removing reliance on the activeworkbook / cell as much as possible, could probably be improved further I.e. get rid of reliance on workbook level variables.

I think you should try to improve the error handling to only trap the specific file not found error

Regards
Ian

Changed/Added code

Please Login or Register  to view this content.

Thanks Ian but I can't seem to get this to work. The data is overwriting on top of each other and the NAME TYPE EQUIP PROJECT is being pasted under the first months. Yup, the data will always be consecutive. I don't really need the grand totals either, my intention is to have all the consolidated data together so I could create a pivot table. I've attached a copy of the report after I ran through the macros

I am sorry you are experiencing difficulties, unfortunately I am unable to replicate the issue, it works fine for me in both Excel 2007 and 2013. Are you using exactly the same data? If not are you able to post the data you are using?

Regards
Ian

I'm using 2013 with the same data and still can't get this to work right, is there a different method that can be used to append the data as it's being copied over? Thanks again, I appreciate the help you've provided