Consolidate 30 sheets into one sheet. very few sheets have one or more extra columns.

Is there a function or macro to consolidate multiple sheets into one sheet? I searched and found the data- consolidate feature but for that I have to go into each sheet select the range. Two or three sheets have extra columns as well as they start in line 3 instead of row two. One sheet have 750 records, one sheet has 5 records soon...

It's one time process so don't want to spend too much time on programming.

Any thoughts

Thank you.

Look at this video on Power Query Consolidation.

https://www.youtube.com/watch?v=HcfKOusOJhA

Hi,

Open your VBA Editor and click on Insert and choose Module.
In the new module window, paste the following code-

Please Login or Register  to view this content.

and then close the VBA editor.
then press Alt+F8 and select the "ConsolidateSheets" macro from the list and Run it.

This code will create a new sheet named "ConsolidatedSheet" and copy all data (excluding the header row) from each sheet (except the "ConsolidatedSheet" itself) in your workbook into the new sheet. The macro will also delete the first sheet (Sheet1) after the consolidation, but you can comment out or remove that part if you want to keep it.


Note- organize your data in a similar structure on each sheet for the code to work properly.

Best Regards.

@Chris5

Please review the forum rules on code tags. Thanks.