Consolidate / combine data from multiple Workbooks with multiple sheets

[Faris_Khan]

Can someone please propose / share a link to the solution for the following:

I have multiple workbooks (from multiple vendors) that have multiple sheets. All sheets have data in same format ,however, they are not in a Table format. Option to receive the workbooks/sheets in a Table format is not available either. Each sheet has a list of items (in hundreds) and they are grouped by cost type. Number of line items are exactly same both within the workbook and the sheets. There are 4 columns against the line items i.e. Unit Rate, Discount, Qty and Amount.
I am looking to automate as much as I can the consolidation of these workbooks for price comparison purposes. What I am looking to do is to just be able to save all the workbooks in a common folder and run the automation (Macro, Power Query etc). Aim of the automation is to put the above-mentioned columns at a sheet level from all the workbooks onto a single sheet meaning the consolidated workbook will also have multiple sheets where each sheet captures data from the other workbooks with same sheet names (e.g. Site 1, Site 2, Site 3).

Key Note: The number of sheets can vary from 1 to 10 in different instances, however, for each individual instance, the number of sheets/sites will always be same i.e. vendors are bidding for same numbers of sites in each instance.

Thanks and Regards
Faris

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.