Split an Excel File in to Many using VBA [Videos]

Posted on October 19th, 2011 in VBA Macros - 29 comments

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Splitting An Excel File in to Many using VBA - Example Data

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps.

  1. Identify the split criteria and list down all values in a small range. In our case, we list all the salespersons names in a named range lstSalesman.
    List of all salespersons - Splitting data in to multiple files using VBA
  2. Set up advanced filters so that we can filter the data by one salesman at a time.
    Range where advanced filter criteria will be specified - Splitting data in to multiple files
  3. Now, for each salesman, apply advanced filters and set it to copy the filtered values elsewhere.
    1. Copy the filtered values
    2. Add a new workbook and paste the copied values there.
    3. Save the new workbook with a unique name
    4. Repeat the above 3 steps for each salesman
  4. That is all! You are done splitting.

Video Lesson on Splitting Data using Filters & VBA

Since splitting data in to multiple files requires a bit of macro code & advanced filter knowledge, I have created a short lesson explaining how this works. Watch it below.

[If you are not able to see the video, watch it on our Youtube Channel]

If you are new to VBA, take our crash course.

Download Split Data Example Workbook

Click here to download the split data example workbook.To use this,

  1. Save the downloaded file to any folder.
  2. Open the file and enable macros.
  3. Examine everything and when ready, click on “Extract” button.
  4. Check the folder where you saved the file and you will fine 4 new Excel workbooks named after the salespersons with the data extracted for them.

You can find the macro code in Module 1.

How do you Handle Splitting Situations?

In my work, I rarely had to split data. And whenever I had to split data, I usually copy paste the data after filtering what I want. But I can imagine many real life scenarios where you need to automate the splitting part.

How do you split data? What techniques and ideas you use to speed up the splitting process? Please share using comments.

More on Splitting & Consolidation

If you are in to splitting or combining things, we have a selection of tips & examples to help you. Check out these articles.

PS: Heck, we have even have an Excel tip to tell you how to split expenses among friends 😛
PPS: You can use Pivot Table Report Filters if you want to split data in to multiple sheets.

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

29 Responses to “Split an Excel File in to Many using VBA [Videos]”

  1. noone says:

    Great. But instead of a fixed salesman-list I would copy the salesman-column to a new sheet, remove duplicates and use that as "lstSalesman", so it will be more dynamic.

  2. Martin says:

    I may have got this one entirely wrong, but... wouldn't be easier to set up either a PivotTable or a Sumproducts table?

    Ok, maybe easier is not the best word here, nut you know what I meant !!!

  3. Jon says:

    If performance is an issue for this type of activity i will typically use a combination of scripting dictionaries and arrays.

    The scripting dictionary takes the form of Category (Sales person name in your example) as the key, in the item in the dictionary is an array of data.

    The program flow will loop through each row.... adding new sales people to the scripting dictionary as they are encountered, then adding the data to the appropriate array.

    Once all the data is captured, loop through the dictionary creating a file for each entry then paste the array into the workbook.

    Not nearly as general purpose as the solution you have outlined above, but it will be a fair bit faster.

  4. Fred says:

    I have done something similar for sales rep, but they are going from one summary sheet to individual customer worksheet on the same workbook. Each sales rep has a portfolio of companies which has multiple contact persons. So it was a simple linkages and vlookups.

  5. Elmer says:

    This is fantastic!
    I am wondering how to split data charts to individual xls files?

  6. alejo says:

    Nice tools....i using this in my work everyday for listing compare, filtering...

  7. sujay says:

    This is really amazing tool. Thanks for sharing it. I have used it in one of my project but it is getting into loop for me. The excel sheet contains lakhs of data. It shows me it is processing but it never ends. I have to abruptly log off from the system to come out of the loop. Please help me on it.

  8. Balakumar says:

    This works Great.. I wish this VBA script is further enhanced to automatically sent the file through mail to concern person. Kindly let me know if that is possible.

  9. Abdul Aziz says:

    Thanks for the example. I have a spreadsheet with 209,592 records relating to account numbers. There are 593 unique account codes. I was wondering how to split this one worksheet to many other work books based on the account code field.

    I have tried using the example you provided, but it seems to be taking up the memory and the destination workbooks come up empty.

    Also, is this possible in Access?

    • Livken says:

      hi Abdul Aziz,
      was you able to replicate the code? I also need to split huge amount of data with ~300 unic codes, but the path does not work for me. I tried to switch the row data and get results for 4 first values, any clue how to expend the range?
      thanks in advance for hint!

  10. Rana says:

    Hi Guys,
    Have succefully implemented this code in my reward spreadsheet. However need help.

    I have Business Units and these business units have various Accounts. I want a code that can populate a business unit sheet and related accounts on individual sheets within the business unit workbook. I hope I have explained myself well.

    Please help.

    Rana

  11. Prince says:

    The code does not paste formulas in the new spreadsheet. Can you please help me paste formulas?

    Sub breakMyList()
    ' This macro takes values in the range myList
    ' and breaks it in to multiple lists
    ' and saves them to separate files.
    Dim cell As Range
    Dim curPath As String
    curPath = ActiveWorkbook.Path & "\"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each cell In Range("BizU")
    [BU] = cell.Value
    Range("myList").AdvancedFilter Action:=xlFilterCopy, _
    criteriarange:=Range("Criteria"), copyToRange:=Range("bizextract"), unique:=False
    Range(Range("bizExtract"), Range("bizExtract").End(xlDown)).Copy
    Workbooks.Add
    ActiveSheet.Paste

    ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Range(Range("bizExtract"), Range("bizExtract").End(xlDown)).ClearContents
    Next cell

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub

  12. [...] Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. Split an Excel File in to Many using VBA [Videos] | Chandoo.org - Learn Microsoft Excel Online [...]

  13. VENKY says:

    This almost did my job... but i wanted further classification...

    like there will be a file for JOSEPH which will have worksheet containing data for NORTH WEST AND MIDDLE.
    Can you please guide...

  14. Wellington says:

    Here in my work we are doing the Budget 2013 per salesman, so it was very useful.
    But now, I need a code to collect all the files.
    What string have I to change to do it?
     
    Very Thank you.

  15. Benny says:

    This example is fantastic. Thanks a lot Chandoo.

    I have one additional question:
    I would like to use this example as a template. So what I would like to do is:

    1. Defining the Advance Filter & the VBA Code in a seperate Excel code (as template excel)
    2. Everytime when I obtain a new Source excel which will be every two weeks, coping the source file to the same folder like the template one and spliting it from the Template excel.

    The reason is:
    I do not want to define the Advance Filter & VBA over and over. Therefore, instead of that. I would like to use the:
    1.  Template excel as TARGET
    2.  SOURCE File
    3. The Template file reads the Data from Source File and Split it to multiple excel files.

    I hope you can help me with that.

    Thanks in advance.

    Benny

  16. waseem says:

    Thanx a lot man...it really saved lot of my time...thnx

  17. Michael says:

    Nice video. Very clear and concise. I'm very new to VBA but I like your solution. Another option, would be to save the excel data as a csv file & then grep for each name and direct contents to another file.
    Thanks

  18. Sundar_shammu says:

    Thank you 🙂

    i am new to Macro; and this is working for me well and would like to know if the below listed things are possible

    1. I want to add the pivot table into separate tab after splitting the file

    2. I do not want to have date and time in my file name

    Pls help me

    thanks a lot in advance

    Sundar_ Shammu

  19. 656jamie656 says:

    Hi My Question is a bit complicated - I have watched the video at http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/
    but I can't make it work for my specific problem (below)

    I have a forecasting spreadsheet containing input from 25 people. Each month I take a master spreadsheet and divide it up into separate files for each person based on the name of the sales person in column A from rows 8 to 1000 in the "input" tab

    The VBA macro I am looking for will copy all rows which contain an individual sales person in the "input" tab, paste them into a template I have created on my C drive, name the file according to the sales person name and loop through repeating the process for each sales person then close the master spreadsheet

    I would like for the data not to remove the cell formatting when it is pasted into my template. Can anybody help please?

    I'm a VBA novice and so any help would be appreciated

    Thanks
    656jamie656

  20. kishore says:

    How will i have to change the copy location?

  21. Ruby Red says:

    Hi. This is exactly what I need to do. How did you create lstSalesman in your spreadsheet? I tried to use your code, but am stuck on this. Did you make it a Table? How did you name it?

    Thank you for any help you can give, I am just starting to create Macros.

  22. BT says:

    Thank you for this. You saved me hours of work. Such a simple and straight forward solution.
    Cheers

  23. A says:

    Great stuff. A few quick questions I was hoping somebody could assist with:

    1). Is there a way to automatically name the tab without going into the file?
    2). Is there a way to autofit the column size of the generated files without going back into the file?
    3). Is there a way to keep adding tabs to the same workbook if you're working with multiple data sets? For example you have 3 salesman data sets and want each file you're sending in the to the salesmans to have the three different tabs.

    Thank you for the help.

  24. Asghar says:

    Hi,
    Tried to use the formula. It doesn't work for me. Formula is not copying desired data. It copies only one line. Anyone has the same issue and how to fix this?

  25. Riaz Ahmed Sumon says:

    It's great. But it export only one sheet of workbook. I want multiple sheet of a workbook will split based on row value. Please help.

  26. Riaz Ahmed Sumon says:

    somebody please please help

  27. Kengputra Ekawijaya says:

    After split the file, can we send each splitted to file to specific person via email using macro.

    In this case,
    We would like to send the report to Joseph's email. Right after the file splitted. Can we do it? please help.

  28. Missy Burnside says:

    I have been in need of doing this exact thing for several years, and today my google search lead me to your video and website. I'm not quite sure how I haven't found it sooner!! Regardless, I'm eternally grateful that I did!! I am forever in your debt :)!! You have just made my job so much easier!!! There is so much about Excel that I still have to learn, and I now know from whom I'll be learning it!! Thanks again for this very valuable lesson!!

Leave a Reply