Macro to Merge different Excel files into one

**sukanyae** · 02-02-2010, 11:20 PM

Hello,

I have 60 excel files with single sheet and I want to merge all 60 files into one excel file with 60 tabs. Is there any way to do it with macros ??

Appreciate your help !!!

Thanks,
/Sukanya

**JBeaucaire** · 02-02-2010, 11:37 PM

I have a standard Consolidation macro to do this. Edit the lines marked in red to your needs and it should do what you want.

Please Login or Register  to view this content.

**sukanyae** · 02-03-2010, 03:12 AM

Hi JBeucarie,

Thanks so much for the code. It is working. But I want the sheet/tab names as actual sheet/tab names from all excel files. Currently It is taking file names as Tab names.

Is that possible ?

Thanks,
/Sukanya

Originally Posted by JBeaucaire

I have a standard Consolidation macro to do this. Edit the lines marked in red to your needs and it should do what you want.

Please Login or Register  to view this content.

**JBeaucaire** · 02-03-2010, 12:25 PM

Of course, but are you 100% positive there will be no duplication of tab names between any two sheets? Absolutely 100%? It really makes me nervous opening 60 "unknown" files and moving in the only sheet in each and not using a sheet-naming method we know in advance won't result in duplicate sheet names.

Please advise.

I've highlighted in blue in my post above the line of code you could remove to have the sheets NOT be renamed prior to importing.

NOTE: Please don't use the QUOTE button, it duplicates my post completely inside of yours for no reason. Just use the QUICK REPLY box below. Use the QUOTE if you do want to quote something specific I've said, but only leave in that portion, remove the rest.

This makes thread much less cumbersome to read.

**premmi** · 06-12-2011, 05:12 PM

Hi Jerry, you macro is very helpful. I have one quick question. what if I just want to combine all the files into one single work sheet ( a new file but all on one tab)?

**Abhilash B** · 07-06-2012, 10:07 AM

Hi Forum,

Thanks so much for the code. It is working. Currently in the macro we have given a path to the files, so anyone using the macro has to create the same folders etc. I wanted to change it like once we run the macro it should ask for the path, like it should only point to dir and then we can select the path where the files are saved.

Also I want to clear the color (unfill) on the final workbook.

Is that possible ?

Thanks,
Abhilash

**arlu1201** · 07-06-2012, 10:27 AM

Abhilash B,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.