Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet

I realise this is likely a mamoth task for an Excel VBA and it should really be a database query, however at present, my only option is multiple linked ranges, or a macro to pull to one range.

What I need to do, is to copy (not cut, as they need to persist on the original document) any populated rows in range D9:X353 on a sheet named ROI, from all workbooks in a certain folder, and paste values into a data sheet on a master report (empty cells below headers on master start at A2 to U2 and continue as required to capture all data).

What I envisage the macro doing is as follows:

- On Data sheet in Master Report, find first populated cell below header (row 1)
- Select first populated cell to last populated cell in range (A to U) and delete contents
- Select first empty row in range below header (A2)
- Open first workbook in Individual Trackers folder
- Select sheet 'ROI'
- Select populated rows in range D9:X135
- Copy
- Paste values into first empty row in range on Master Report, Data sheet
- Close Individual Trackers workbook
- Find next empty row and repeat until all workbooks in Individual Trackers folder has been copied and pasted.

Is this doable, or am I better off just leaving my data sheet as it is at the moment and adding a new linked range in for each new individual tracker created?

Many thanks for any help!

Jenn

This should do it:

Please Login or Register  to view this content.

WE can apply an AUTOFILTER to the source workbooks. Since we're copying from row 9 down, this means we can use row 8 for the "filter". So, you want to filter everything out that is blank in column D?

Please Login or Register  to view this content.