VBA to extract data from multiple workbooks into one consolidated document

[The_Snook]

Morning all,

I’m due to receive a number of supplier bids shortly and I’d like a macro that will do all the heavy ‘lifting and shifting’ to create a consolidated price summary. All the bids received will be placed into a single folder and so I’d like the macro to loop through the open, extraction and closure process for each workbook.

Attached is an example of the bid template and consolidated summary. The consolidated summary workbook also contains the data mapping that is required. It lists the tab and relevant range from which the data is to be extracted from the bid workbooks. Directly to the right of that is the destination mapping for where the data needs to go in the consolidated workbook.

I hope this makes sense? If you have any queries or require any further information just give me a shout.

Thanks in advance,

Snook

[bakerman2]

This should get you started.
Just change SourceFolderName to correct folder with all submissions.

Please Login or Register  to view this content.

[Olly]

An alternative approach, using Power Query to combine the data, then output into a pivot table, formatted to suit:

Query: Combine Data In Folder (change folder path)

Please Login or Register  to view this content.

Query: fnEachWorkbook

Please Login or Register  to view this content.

Query: fnService

Please Login or Register  to view this content.

Query: fnProduct

Please Login or Register  to view this content.

Query: fnProject

Please Login or Register  to view this content.

See attached file for worked example.

[The_Snook]

Thanks both, much appreciated.

Given the time constraint for this task I'll give Bakerman2's solution a whirl and will let you know how I get on.....

Thanks for your alternative solution though Olly, Power Query is something I keep meaning to take the time to understand but never get around to. This solution gives me a reason to set the ball rolling on it.

Regards,

Snook

[The_Snook]

Is it possible to tweak the code so that it identifies which supplier cells have been populated (across the 'service', 'product' and 'project' tabs) and return one of those names? At the moment it is picking it up from cell C2 on the 'Service' tab but if a supplier isn't bidding on that work then the cell will be blank.

Cheers,

Snook

[bakerman2]

Try this one.

Please Login or Register  to view this content.

[The_Snook]

Cheers bakerman2, it works a treat!

Snook

[bakerman2]

Glad to help and thanks for rep+.