+ Reply to Thread
Results 1 to 14 of 14

Merging multiple excel files in a folder into one master worksheet in a new worksheet

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Hello,

    I'm looking for a visual basic code which will combine multiple files in a folder called 'Freight' all of which have the same layout and design, but each of which contains different data under the headings and a different amount of data in each file.

    The title in each file takes up the first 3 rows, the data typically goes from column A to AE and possibly from row 4 to 100.

    After copying all the files into the master worksheet I would then like the worksheet to be sorted by invoice date, which in this case is in column X.

    My idea is that by sorting the data it will sort all the blank entries to the bottom of the worksheet so there won't be any gaps in the data. The files may have different names so I would like the code to copy all the files in a specific file path instead of just naming the individual files. I would also like the title on the master file but because the title is the exact same in every file and takes up the same rows I only need it copied from any one file.

    The current code I have combines all the files into one master workbook but it copies each individual file into a separate worksheet, which is obviously not what I am looking for.

    Anyone up to the challenge?

    I appreciate any help I can get

    Johnny_Canuck

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Johnny_Canuck,

    Give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    It was able to copy the data from the different spreadsheets in my destination folder and then sorted it by column X (both of which worked perfectly) but it didn't copy over any of the titles which is extremely important, it also copies the data into the same spreadsheet where the macro was written, it isn't such a big deal but I was hopeful that the code would create a whole new spreadsheet other than the one where the macro is. Once again your help is appreciated.

    Canuck

  4. #4
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    I don't know if it would make it easier but it would almost be better if it took one file from the folder and copied the other data into it, that way the format of the spreadsheet and the columns would be the same and the titles would be there.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Where should the titles go?

    [EDIT]:
    Just saw your last post, that is possible, will work on it

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Updated code:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    rows 1-3 and columns A to AE, I have attached the template that I am using to give you a better idea
    Attached Files Attached Files

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Check the updated and let me know if that is working for you.

  9. #9
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Now the format is perfect but I get an error when I run the macro
    Run time error '1004' This operation requires the merged cells to be identically sized
    and the issue is the code below, the data now doesn't sort

    Please Login or Register  to view this content.

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    VBA and merged cells don't mix well, I would recommend avoiding the use of merged cells at all times. As for updating that line, give this a try instead:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Now I get another error when I change the code, I think I'm just going to stick to the updated code and have to deal with manually sorting the data after running the macro, thanks

  12. #12
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Is there a way to change the code so that instead of automatically consolidating all the files in a specific file path the macro prompts you to choose the file path after running the macro?

  13. #13
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-01-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Merging multiple excel files in a folder into one master worksheet in a new worksheet

    Can't thank you enough!

+ 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. Merging specified worksheet in multiple files into 1 merged file
    By RebeccaEllis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2012, 11:22 AM
  2. Merging particular worksheet from Multiple Files in a folder into a single workbook
    By excelhelp18 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2012, 01:37 AM
  3. merging multiple excel worksheet into 1 worksheet
    By jimb01 in forum Excel General
    Replies: 3
    Last Post: 07-31-2011, 04:25 PM
  4. Merging multiple files into one master
    By vancoservices in forum Excel General
    Replies: 2
    Last Post: 07-08-2010, 03:38 PM
  5. Merging multiple files into 1 worksheet
    By markie in forum Excel General
    Replies: 1
    Last Post: 12-18-2006, 01:23 PM

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