+ Reply to Thread
Results 1 to 5 of 5

Combining multiple workbooks into one master workbook as tabs

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Combining multiple workbooks into one master workbook as tabs

    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!!

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: Combining multiple workbooks into one master workbook as tabs

    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.

  3. #3
    Registered User
    Join Date
    07-11-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Combining multiple workbooks into one master workbook as tabs

    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!
    Last edited by bidgeman; 07-12-2011 at 12:04 PM. Reason: added info

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: Combining multiple workbooks into one master workbook as tabs

    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.
    Attached Files Attached Files
    Last edited by p24leclerc; 07-12-2011 at 09:00 PM.

  5. #5
    Registered User
    Join Date
    07-11-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Combining multiple workbooks into one master workbook as tabs

    That worked BEAUTIFULLY! Thanks VERY much once again - you are the best!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1