Combining multiple workbooks into one master workbook as tabs

**bidgeman** · 07-11-2011, 01:57 PM

Hello,

I'm sure this question has been answered in a million various forms but what I'm looking to do is pretty simple (based on other solutions I've seen for similar problems).

Here is the problem: I have 106 excel workbooks (each in the same format) that I need to combine as tabs into one master workbook. Each tab should be named whatever is in cell E2 of the associated workbook. I am using Excel 2007 but all workbooks are in 2003 format; not sure if this makes a difference or not.

Would someone be able to give me script that would do this?

I appreciate any help!!

**p24leclerc** · 07-11-2011, 08:44 PM

You have to place all the workbooks in the same directory as the file it will be merged into. It may take a while to perform all the merge. Copy this code into your merging file and run it once.

Please Login or Register  to view this content.

**bidgeman** · 07-12-2011, 10:19 AM

Hello p24leclerc!

Thank you VERY much for responding - sorry to say that the macro isn't working correctly, but probably because I didn't give you enough information.

When I run it I get a "400 Error" but I'm thinking it's because of one of these things:

- My main file name (where I want all the sheets to roll into as tabs) is called "Master"

- Each file in the directory has a different name, all in numbered order. For instance it starts with file name "TA-1" and goes all the way up to "TA-365" (with one file named differently from the rest as "TA-3A").

- Also, when running the macro it looks to be copying the first sheet over okay before getting the error but it's naming the tab the name of the tab that the original data came from (which they are all named "A"), not what is in E2.

Sorry for not being specific enough the first time!

Thanks again!

**p24leclerc** · 07-12-2011, 08:58 PM

Hi,
here is the file I worked with. Name has no effect on the macro.

Place it in the same directory as your other files and run the macro Create_tabs. The macro is not sensitive to your file's names.

Make sure all files have a different content in cell E2 as no 2 sheets can have the same name. But it would give you a Run time error 1004 not a 400.

Try this file and tell me if it works.