How Best To Move Lots Of Data From One Workbook To Different Cells On Another Workbook

[Bingo969]

Good Afternoon -

I'm using Excel 2010 and Win7.

I need to move data from multiple workbooks that often have some different formats over to a standardized template.

I feel there has to be a way to somewhat automate this. The biggest issue is that not all the fields on the template will appear on the various workbooks.

I have a formula that will call data from other workbooks, even if closed. For each vendor, all invoices and the related template will be in a dedicated folder (IE - Vendor1 invoices and Vendor1 template will be in a folder. Vendor2 Invoice and Vendor2 template will be in a folder, etc).

The positive is that ALL invoices are subtotatled, so I know the word "subtotal" will always appear on any line I need to get data from.

So say for vendor 1, is there a way I can search for the string 'subtotal', count over 4 spaces to the right and then return that value. Then count over 8 spaces from 'subtotal' to find a second value. Then continue to find the next occurance of the string 'subtotal', count over 4 spaces, return value, 8 spaces, return value and so on until it doesn't find 'subtotal' any more?

I hope this makes sense. Thank you for any suggestions you can offer.

 

[Bingo969]

Just a wee little bump in the hopes the morning crowd might have any insights.