+ Reply to Thread
Results 1 to 15 of 15

Combine multiple workbooks into one master spreadsheet

  1. #1
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Combine multiple workbooks into one master spreadsheet

    I am trying to combine information that is coming in from multiple spreadsheets into one master spreadsheet. I do not want to use the consolidate button because I am simply going to be copying words. The main catch is that I want to have the master spreadsheet update as the information changes from the other spreadsheets.

    For example: Spreadsheet #1 has the information:

    Name Username
    James Blue jblue
    John Parker jparker

    Spreadsheet #2 has information:

    Name Username
    Greg Hatch ghatch
    Liam Anderson landerson

    And I want the master sheet to have:

    Name Username
    James Blue jblue
    John Parker jparker
    Greg Hatch ghatch
    Liam Anderson landerson


    However, if I edited spreadsheet #1 and added a name:
    Spreadsheet #1:

    Name Username
    James Blue jblue
    John Parker jparker
    New Person nperson


    I want the master spreadsheet to look like this:

    Name Username
    James Blue jblue
    John Parker jparker
    New Person nperson
    Greg Hatch ghatch
    Liam Anderson landerson


    Thanks in advance!

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    Here is a sample workbook that shows how to construct a Master worksheet that combines the sub worksheets.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Combine multiple workbooks into one master spreadsheet

    This VBA code should also work.

    Please Login or Register  to view this content.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  4. #4
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Re: Combine multiple workbooks into one master spreadsheet

    Thanks! Would you mind explaining how this works?

  5. #5
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Re: Combine multiple workbooks into one master spreadsheet

    I'm not that familiar with VBA...can you tell me how i should put this in?

  6. #6
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Combine multiple workbooks into one master spreadsheet

    I put it in this sheet for you. You would just need to open the VBA editor, and paste the code into a new module. I also went to developer, Macros, Options, and made a shortcut key combination so you can run the macro easily. On this sheet you would press CTRL+SHIFT+U and the master sheet will update with everything from the other sheets.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Re: Combine multiple workbooks into one master spreadsheet

    Also newdoverman, is there a way to do this between multiple workbooks? what would i do to change that? thanks.

    nigelbloomy thanks for the VBA code! i'm going to see if i can do it without that too.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    I have never attempted doing this with more than one workbook. There is likely a way to do it but I have no confidence that it would be easily set up or easily maintained. The formula solution is based on consecutive numbering between worksheets and I think that it would become problematic maintaining this between workbooks.

    I think that if you have a VBA solution that works with multiple workbooks, use it.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    I just did a test using the file that I sent to you and used two worksheets from another workbook and it was much easier than I had thought. There is a complication in that if you add worksheets to the workbooks, it requires some adjustment between the workbooks to keep it going.

    Here are two linked workbook with linked worksheets. Both have to be open to work. There is a link going both ways to update the consecutive numbers and to extract the results.

    The references will likely be messed up in column R of the Combine Worksheets....workbook.
    R6 of this workbook should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    R7 of this workbook should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Once fixed, the worksheet should work properly.
    Attached Files Attached Files
    Last edited by newdoverman; 06-15-2015 at 08:53 PM.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    Just verified by downloading the files. Save the files to where you want to have them, change the references in column R. You may have to change the MAX formula in the sub workbook if the numbering is showing an error. Otherwise, it seems to work fine (surprise surprise )

    When adding worksheets, it will be easiest if the new worksheets are added to the end of the workbooks as the numbering follows from worksheet to worksheet.

    Other workbooks can be added in the same manner as the second workbook. The references in columns Q and R have to be added for the master worksheet to work. No changes in the first columns will be needed other than to add the correct number of columns for your data. The way that the current formula is written for columns C and D, the formula can be filled across the correct number of columns without changing the formulae and then filled down as far as required.

    I saved a copy of this exercise for future reference and once saved to where I wanted the files, they work perfectly together.

  11. #11
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Re: Combine multiple workbooks into one master spreadsheet

    When I try to change the formula in R6 and R7 it always goes back to the pathway of C:\Users\....\[sample-ndm.xlsx]

    Then when I added a person into Sheet 2 of the combine worksheets workbook it created Reference errors for the other workbook.

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    I have created this archive of two files that work together on my system to see if this method will work when downloaded.

    Didn't work right off. I opened the archive and then opened both files at the same time and enabled editing. I had to change the formula in R6 to:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and change the formula in R7 to:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If there are still errors, make sure that the formula in Sample - ndm.xlsx sheet2 is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Once that is done, all errors should disappear and when you save the workbooks they should open and work correctly. The two-way links is the problem but when corrected, the setup works fine.
    Attached Files Attached Files
    Last edited by newdoverman; 06-16-2015 at 03:34 PM.

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    This formula in R3 and filled down might work better for you. It creates the link using the values in column Q to create the link formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    6

    Re: Combine multiple workbooks into one master spreadsheet

    Ok I figured it all out and it works great! Thanks so much for all your help!

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Combine multiple workbooks into one master spreadsheet

    Thank you for the feedback. Give it a good workout before implementing if this is the solution that you want to use.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Combine several workbooks containing data in multiple sheets into a master Workbook
    By sunrize9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2014, 09:10 PM
  2. Excel 2010 : Combine Multiple Workbooks into One Master Workbook
    By tehjagjr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2013, 05:07 PM
  3. Replies: 8
    Last Post: 02-28-2012, 12:04 PM
  4. Combine multiple data sets into one master spreadsheet
    By thetrickster in forum Excel General
    Replies: 5
    Last Post: 01-16-2012, 02:55 PM
  5. Replies: 8
    Last Post: 09-24-2011, 05:16 PM

Tags for this Thread

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