Closed Thread
Results 1 to 9 of 9

Merging multiple workbooks on to one master workbook

  1. #1
    Registered User
    Join Date
    04-05-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Merging multiple workbooks on to one master workbook

    Hello Programmers!

    I am looking to link 6 seperate workbooks to one master workbook. Each workbook has 4 tabs, which are all identical to the master. Please see attached a copy of the individual workbook with a copy of the master spreadsheet where it will all be inputted.

    Any help would be much appreciated as I'm a super VBA/Macro Noob!

    Please let me know if you need anymore information.

    Thanks :-)

    Alex
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Merging multiple workbooks on to one master workbook

    'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
    Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.The parts of the code that need to be edited are colored to draw your attention.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-05-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Merging multiple workbooks on to one master workbook

    Hi Jerry,

    The link you posted is to link multiple workbooks with multiple sheets to one workbook with multiple sheets. Actually what I'm looking to do is link multiple workbooks with multiple sheets to one sheet on one workbook.

    Do you have a code for this?

    Looking forward to hearin back from you.

    Thanks :-)
    Alex

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Merging multiple workbooks on to one master workbook

    Here's a tweak on that macro to work for all sheets in all workbooks into a single MAIN sheet:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-13-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Merging multiple workbooks on to one master workbook

    Quote Originally Posted by JBeaucaire View Post
    'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK
    Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.The parts of the code that need to be edited are colored to draw your attention.
    Hi Jerry, is there a way of using this code so it assumes data starts on, say, row 8 of each sheet (not row 2 as it currently assumes)? I have only discovered what Macros are today so please forgive me if this is a stupid question! Additionally - if each week i reopen my master spreadsheet to analyse, will it run the macro and only add new rows or will it add all the rows again? Thank you

  6. #6
    Registered User
    Join Date
    06-13-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Merging multiple workbooks on to one master workbook

    Sorry, please ignore my previous post ... i have now figured it out and the range now starts from row 8.

    For my weekly analysis, i shall delete the previous weeks' master 'macro' data and simply re-run the macro, unless someone can suggest otherwise.

    Thanks for your previous posts - very helpful.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Merging multiple workbooks on to one master workbook

    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

  8. #8
    Registered User
    Join Date
    06-13-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Merging multiple workbooks on to one master workbook

    Hi Jerry,

    I thought i'd finally managed to create what i was after using a macro i found. However, i've found that the macro i have used only gathers filtered data - i need it to capture all data from the various workbooks' sheet.

    To explain further, if 'Sarah' filters data on her spreadsheet (for her personal reference; the invidual spreadsheets are used a lot on a day-to-day basis), when i run my 'master' macro it will only gather what she has filtered at that time. My master spreadsheet therefore will miss data.

    Is there a way to amend the macro so that it captures all data, no matter what filters are on/off in the spreadsheets:

    Please Login or Register  to view this content.
    Many thanks!
    Last edited by arlu1201; 06-15-2012 at 02:06 PM. Reason: Code tags

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Merging multiple workbooks on to one master workbook

    Nate, we're here to help, but you really must adhere to the forum rules. Be sure to read through them (link above).

    1) Post threads of your own, do not redirect someone else's existing thread with separate questions of our own (called hijacking). Start your own thread, include links to other threads if you really think they're relevant.

    2) Code posted in a forum must be between CODE /CODE tags. ( see my signature for example).


    If you would please post your own thread with good title and code tags and whatever, feel free to Private Message me a link to that thread to grab my attention to it. Thanks! See you there.

Closed 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