fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

45 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!!

  29. Chirag raval says:

    Dear All,

    There are “Fixed Criteria Range” used in this macro
    That force you must use 4 salesman’s name in “lstSalesman.” Named range
    If you require just 3 or 2 Salesmans’s data..this process goes to Error..
    Or All salesman data generated …
    Is there are way to give “Dynamic Criteria Range” ?
    Like CriteriaRange:= _
    ThisWorkbook.Sheets("sheet1").Range(Cells(1, "J"), Cells(Rows.Count, "J").End(xlUp)), Unique:=False
    So we can generate files for only required buyers.

    Regards,
    Chirag Raval

  30. sandeep says:

    it was great experiance this is so much useful
    on regularly am working on excels i need more information about VBA scripting so can u share ur number so that we can discuss more
    i want to take training from you

  31. Chris Prazak says:

    Really enjoyed this code. Makes monthly reporting very quick now as I can pull a single consolidated list of all customers and then create individual reports for each. Two questions that I have..

    1. Is there a way to add totals to columns that are exported
    2. Is there a way to run a report for two salesmen at the same time? (Joseph and Maria)? I have tried a few things but no luck.

    Thank you

  32. Cherry 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... ....IT IS VERY VERY VERY USE FULLL.... THANK YOU......

  33. Mohit says:

    I want to paste the data in a specified location instead of cell A1 and add some values to other cells and then save it ...Also do some formatting can anyone help?

  34. Tilsh says:

    Hello,

    Great... Thank you so much chandoo...

    I want the same split macro, but need some modifications. Will you plz help me in this.... ?

    Can you share me your mail id so that I can share u my data..

  35. Karlo says:

    Hi Chandoo,

    I have a variation on this. Below is a VBA that I have been using for a while to split and save worksheets as different books. My needs have changed however. I would like to only save the active sheet as a new book. I have tried to change the part that says "For Each sht In ThisWorkbook.Sheets sht.Copy" to "ActiveSheet.Copy" and removed the "Next sht" argument but I get an error.
    Could you tell me what I'm doing wrong?

    Sub Splitbook()
    MyPath = ThisWorkbook.Path
    For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
    ActiveWorkbook.SaveAs _
    Filename:=MyPath & "\" & sht.Name & ".xls"
    ActiveWorkbook.Close savechanges:=False
    Next sht
    End Sub

  36. Raghu Prabhu says:

    Hi Chandoo,

    I am saving my activeworkbook as xlsx files according to the values in column N. It is working fine except for not closing the last file formed.

    What have I done incorrectly.

    Sub SaveAsString()
    Dim i As Integer
    Dim lRow As Integer
    Dim sPath As String
    Dim sFileName As String
    Dim wb As Workbook
    sPath = ThisWorkbook.Path
    lRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    For i = 2 To lRow

    sFileName = Range("N" & i).Value
    ActiveWorkbook.SaveAs filename:=sPath & "\" & sFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Next i
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub

  37. subhash choudhary says:

    Please share me the VBA/MACRO code to seperate sheets

    • Hui... says:

      @Subhash

      What does Separate sheets mean?

      Please elaborate

    • Raghu Prabhu says:

      Hi Subash,

      Here is the code I was able to figure it out.

      [code]
      Sub SaveAsString()
      Dim i As Integer
      Dim lastRow As Integer
      Dim sPath As String
      Dim sFileName As String
      Dim oFilename As String
      oFilename = "zMaster" ' Change "Book1" to the name of the original workbook
      sPath = ThisWorkbook.Path
      lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

      Application.DisplayAlerts = False
      Application.ScreenUpdating = False

      For i = 2 To lastRow

      sFileName = Range("N" & i).Value
      ActiveWorkbook.SaveAs fileName:=sPath & "\" & sFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

      Next i

      Workbooks.Open fileName:=sPath & "\" & oFilename & ".xlsm"
      Workbooks.Open fileName:=sPath & "\" & sFileName & ".xlsx"
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      ActiveWorkbook.Close
      End Sub
      [/code]

  38. Naveen Verma says:

    Your functionality of one workbook to create many is awesome just need one more support, The code does not paste formulas in the new spreadsheet. Can you please help me paste formula too.

  39. Hirvita says:

    i want to add a common dropdown list for all these excel sheet that i will make after clicking extract button is there any way to code it ?

  40. Rajan Kumar says:

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

    but Dear sir, i want to save as PDF Fixed Files these split excel files,please suggest how can i do this...

  41. my file contains multiple sheets need to split all sheets how can it be done please help

  42. Harshit Shah says:

    I have a file with multiple excel sheet & wants to split entire excel file with the same multiple sheets based on a columns of different sheets...

Leave a Reply