+ Reply to Thread
Results 1 to 6 of 6

Combining multiple workbooks into one workbook

  1. #1
    Registered User
    Join Date
    11-19-2018
    Location
    Estonia
    MS-Off Ver
    2013
    Posts
    2

    Combining multiple workbooks into one workbook

    I would like to combine multiple workbooks into one workbook (I even got myself KUTOOLS) but I just can't figure out how to do it!

    To explain what I need Excel to do, I made up an easy example that explains my situation! The data in the Excel workbooks are written sentences, all workbooks have the same column headings for example letter, name, aadress, telephone.

    Different people have filled the Excel table in with their name, aadress, telephone according to their first name letter. Lets say there are 20 of these workbooks. Each person has written their data in different rows, so for example Adam used row 1 for the letter A, Bethany used row 2 (left ohter rows empty) etc. Now I would like to combine all these 20 workbooks into one.

    It would look something like this:

    Adam sent me this:

    Letter Name Address Telephone
    A Adam House str 63366366
    B

    Bethany sent me this:

    Letter Name Address Telephone
    A
    B Bethany Window str 564767576



    I need to combine them into this:

    Letter Name Address Telephone
    A Adam House str 63366366
    B Bethany Window str 564767576


    I would greatly appreciate any help as I work with situations like these and I just can't figure it out!
    Attached Files Attached Files
    Last edited by Katlin; 11-19-2018 at 12:24 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    12,204

    Re: Combining multiple workbooks into one workbook

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  3. #3
    Registered User
    Join Date
    11-19-2018
    Location
    Estonia
    MS-Off Ver
    2013
    Posts
    2

    Re: Combining multiple workbooks into one workbook

    Thank you for pointing this out! I have now added examples!

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    12,204

    Re: Combining multiple workbooks into one workbook

    I can think of a macro to do this, but you have this in the formulas & functions forum.

    Check out this clip here.

    https://www.youtube.com/watch?v=6XsvI9i2z7s

    Also, the names do not have to be in their own row, just sort the column.

  5. #5
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    24

    Re: Combining multiple workbooks into one workbook

    Hi Dave,

    I have a question regarding this.

    Would this work if my files are in .csv format? I noticed that in .csv format the worksheet names are the filenames. I believe your code picks up the data in worksheets called "Sheet 1" from multiple workbooks.

    Is there a way to change it so that it picks up whatever is in the 1st sheet of the files? - assuming the worksheet names are different due to .csv format.

    *Editing to include this real quick..

    It looks to be this line.

    With Worksheets("Sheet1")

    What would be the new line/lines to add/change if I wanted it to pick what is the 1st sheet(only sheet) in the files?

    Much appreciated!!!
    Last edited by zed369; 11-24-2018 at 03:16 AM.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    12,204

    Re: Combining multiple workbooks into one workbook

    Hi zed369,
    Although you have hijacked somebody else's thread,
    You don't have to use the sheet name, you can use Sheets(1) in place of Worksheets("Sheet1")

+ 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