loop to copy data from multiple workbooks into one

Hi,

I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.

Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.

From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:

Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.

Here is my current code:

Please Login or Register  to view this content.

Currently, this is finding the appropriate workbook to copy from, but doesnt copy anything into the summary report. Therefore, I suspect theres a big problem with the cells it is looking at and then the location of the cell it is looking to place the copied data???

Any ideas would be much appreciated.

Cheers,
Alex

This is much easier using an add-in: http://www.rondebruin.nl/merge.htm

thanks, but I'd prefer to solve this by VBA

royuk,

my current code correctly located the folder and the files within the folder, it just doesnt seem to locate the range of cells I want to copy, and then the range I need to copy them into back in the summary sheet

thanks,
alex

yeah roy, i've got that in my code, and it does manage to locate the file(s) that its supposed to copy data from.

I'm by no means an expert, but I believe theres a problem with the loop, and especially the way in which it is targetting the data to copy, and then where to copy it too.

Each report that I'm copying from has a row of data in cells D11 to Q11, and I need to copy that row to a summary grid in the summary report (attached).

Cheers,
alex

the loop works via GetWorkStreamData, in that it keeps looping back up to open each report.

If you look at the reports in my attachment (first post)...I just think that the range(s) that I'm looking to gather data from is incorrect?

it opens up the Report to PMT from Vauxhall.xls but doesnt copy any of its data and insert it in the summary report

roy,

it shouldnt be doing that. if you could imagine the following......

basically I've got 6 reports that are identical in format, in that they all have a row of data between cells D11:Q11 and headings in cells D10:Q10. The headings are the same for each report.

I've then got a grid in the summary report with the same headings with cells D18:Q18. Within cells B19:B24 I've got the names of the makes of car e.g. vauxhall, ford, fiat, etc.

My requirement is that I pull the data from 'Report to PMT for VAUXHALL' (cells D11:Q11) and paste them into the approriate row in the summary report, in this case D19:Q19.

Ford - data should be inserted in D20:Q20
Fiat - data should be inserted in D21:Q21 and so on and so on....

hope this helps you to understand the problem better?

thanks,
alex

roy,

your a genius! thank you so much!

This problem has been irritating me for days now!

thank you!
alex