+ Reply to Thread
Results 1 to 31 of 31

How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Hello everyone,

    I'm looking for a macro that can do the following:
    • I have a large file and I want to break it up into smaller workbooks based on the value in column H. (So everytime the value changes in column H, I would like a new workbook to be created, containing )
    • I would like the smaller workbooks to retain the headers from row 1 of the original file
    • I would like to save the files to a specific file path (they will all save to the same file path)
    • I would also like the naming convention of the smaller files to be as follows: Value from Column H & " " & Date (in format mmddyy)
    • These files should be in .xlsx format


    Thank you so very much in advance for all your help!
    Last edited by UzieJacuzzi; 07-19-2012 at 05:14 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Do you have a sample file that you can upload?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    'ONE SHEET to WORKBOOKS (1)
    Here's a macro for taking a sheet with data and creating individual wbs from each unique value in a chosen column. The date is added to the workbook names to give a reference as to when the wbs were created.

    You can edit the colored sections for your environment, should take of your need.
    _________________
    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!)

  4. #4
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    All,

    Attached is a sample file per your request.

    CondensedFile2InspLots.XLS

  5. #5
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Hi JB

    Thank you so much for that code! It works wonderfully. I know you have created that code to be flexible which will be very useful if I need it. However, I am lucky enough that I only need to split the files by the value in column H (column 8). Is there a way to automate that portion so I dont have to pick which column to filter by?

    Also, I changed the part of the code to accept any active sheet instead of a pre-defined name. Do you see any negative effects of that on the rest of the code? I ran it and seems to be working fine...

    Again, thank you so very much for the code. Your efforts are very much so appreciated!!!
    Last edited by UzieJacuzzi; 07-23-2012 at 07:12 AM.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Sure, Take that section out and replace it with vCol = 8

  7. #7
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Thanks JB. That worked perfect. To give you a background, I am going to be using this macro and importing files into a Lab Management System for sample logging.

    Just two issues I've run into:

    1. The code you supplied has xlNormal in it. I need the file to be in .xlsx format. Can the code be amended to fix that? I tried one approach but it didnt work for me (probably b/c i'm a microbiologist, not a programmer )
    2. The new workbooks that are created do not have a sheet name. I would like the sheet name to always default to "WORKPLA" (for the newly created workbooks)

    Since I tweaked the code a bit, here it is for your view:
    Please Login or Register  to view this content.
    Thanks again! You've been super helpful!!!

    ---------- Post added at 09:12 AM ---------- Previous post was at 07:58 AM ----------

    JB,

    As an update, I was able to search through google and figure out how to save the file in .xlsx format. However, once the macro executes, every newly generated file opens up (which I dont want b/c that could potentially be hundreds of files when I have large amounts of data). Any ideas? Here is the amended code with what I have thus far:

    Please Login or Register  to view this content.
    In summary, I am looking for two things:
    • (if using the above amended code) How to prevent the newly generated files from popping up
    • How to change the sheet name to "WORKPLA" for the newly generated files.

    Thanks again!
    Last edited by UzieJacuzzi; 07-25-2012 at 09:45 AM.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    So maybe:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    That did not work. In case this is relevant, I am using Excel 2007

    Got the following error:
    Run-time error '1004':
    Method 'SaveAs' of object'_Workbook' failed

    Debug highlighted :

    Please Login or Register  to view this content.
    As you can see above, i also added activesheet name to always default to WORKPLA.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Turn on the macro recorder, let it record you saving a file in the format needed. That will give you the Excel 2007 syntax you will need to mimic.

  11. #11
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Got it to work! Still one problem:

    I am using this file naming convention: YYMM-DD-Value from Column AO-Value from Column H

    Please Login or Register  to view this content.
    Problem is, excel is putting an apostrophe before the numerical value in both, column AO and column H. I want to exclude this from the name. Any way to exclude the apostrophe?

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Why is Excel putting apostrophes in those cells?

  13. #13
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    I'm importing these files from a software called SAP. The apostrophes are there because some of the values in the file begin with a zero. By default, Excel would remove the zero if there wasnt a prime before it and that would be incorrect information.

    For example, a factory could be designated as number: 0433. If the apostrophe wasnt there, excel would make this 433, which could potentially be a totally different factory.
    Last edited by UzieJacuzzi; 07-26-2012 at 11:20 AM.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    So in the part of the code where there is
    Please Login or Register  to view this content.

    ....you could wrap that:
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Perfect, thank you so much for all your help JB. Two thumbs up. This forum is absolutely fantastic because of helpful people like you!!!

    Everything works like it should! I cant thank you all enough for your help & I hope this will assist other people who view this thread too.

  16. #16
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    JB,

    I started testing all kinds of scenarios using the macro and I'm stumped by one of the most obvious ones.
    If my original file only has 1 value, the code breaks. Currently the code looks for different values and creates a file for each value. But if there is only one value, I get the following error:

    Runtime error '13'
    Type mismatch

    Debug highlights the following part of the code

    Please Login or Register  to view this content.
    If I only have one value in column H, I want it to continue making a new file (essentially a copy of the original) with the defined naming convention, etc...

    Thanks! Have a great weekend!
    Last edited by UzieJacuzzi; 07-27-2012 at 01:36 PM.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Does this work:

    Please Login or Register  to view this content.

    If not, post up a smaller version of the file you're trying to process and I'll run you code against it, see if I can trick up something.

  18. #18
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Good Morning JB

    That code didnt work. Attached is the file:

    Test File for JB.xlsx

    and here is my most up to date code:

    Please Login or Register  to view this content.
    Thanks again!

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Try this one
    Please Login or Register  to view this content.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Hi Jindon, that did not work.

    Debug highlighted:

    Please Login or Register  to view this content.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Quote Originally Posted by UzieJacuzzi View Post
    Hi Jindon, that did not work.

    Debug highlighted:

    Please Login or Register  to view this content.
    It's working for me.

    Try this one then
    (also tested already)
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Thanks for the quick reply! Are you replacing the code I had posted entirely? Perhaps I'm doing something wrong.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Quote Originally Posted by UzieJacuzzi View Post
    Thanks for the quick reply! Are you replacing the code I had posted entirely? Perhaps I'm doing something wrong.
    Of course.

  24. #24
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    The macro is executing but it is not creating files

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    It should create file(s) in the same folder that the workbook you pasted the code.

    Delete the line of
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Still the same issue - macro executes but does not create files at the designated path.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Did you try the file I attached in my first post?

  28. #28
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Yes i did...still didnt create the files.

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    OK, can you just try this and see if MsgBox appear or create file(s) in you desktop.
    Please Login or Register  to view this content.

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

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    Here's my old macro with a tweak in it for when there is only one value found.
    Please Login or Register  to view this content.

  31. #31
    Registered User
    Join Date
    07-19-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column

    JB,

    Your code did the trick. Thanks alot! I now pronounce this thread, solved and conquered!

    Thank you everyone for your time and efforts in helping me resolve this issue.

+ 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