MERGE EXCEL WORKBOOS SHEETWISE

[YogeshShambharkar]

Hello masters,
I have multiple workbooks, each with same number and names of sheets. i would like to combine these workbooks by sheets. Data form first sheets of all workbook to be merged/consolidated/combined in to sheet 1, data from second sheets of all workbook to be merged/consolidated/combined in to sheet 2 and so on till the last sheets. doing this manually by coping and pasting data manually is very tedious and time consuming. Is there any way to do this easy way, may be there is a macro for this.

 

[Automatrix]

Yes. It has me set up a sample sheet. And it combines the selected sheet only. but if I have 10 sheets in every workbook then I have to repete the excercise 10 times.

You can copy the M code between the queries, and just change the source step.

 

[YogeshShambharkar]

@jolivanes sorry for quoting again.....

I am pretty new to VBA. I followed your instruction from 1st reply. Saved the code to workbook which will receive all the data. the workbook is saved in the same folder as other files which are to be merged. Sheets are in same order and have similar names in all the workbooks. Now pls guide me.

 

[jolivanes]

"similar" does not do it. Exact same does.

 

[YogeshShambharkar]

@jolivanes
Sheet names are exactly the same in all workbooks. If you want i can share the sheets with you.

 

[jolivanes]

No leading and/or trailing spaces in names by accident?

 

[YogeshShambharkar]

No leading and/or trailing spaces in the names. The files are system generated.

 

[YogeshShambharkar]

@jolivanes
I can share few sheets to you so you can see if something is wrong with the sheets

 

[YogeshShambharkar]

@jolivanes
no reply from you for long time.

 

[jolivanes]

As I mentioned in Post #10, works like a charm here.
If you upload both workbooks into DropBox or similar free site, we can have a look.
If you go that route, make sure workbooks don't have any code in it that change settings ( comment out all workbook_change and similar code) and that, if you have them, sheets/books are free of protection.

 

[YogeshShambharkar]

@jolivanes
Following is the link to Dropbox. It contains 12 files 1 for each month of the year. Pls help.

FILES

Shared with Dropbox

www.dropbox.com