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
- 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!