Introduction to Excel VBA - Module 13 (Automatically Split Rows into Different Workbooks) 

great work SAF Business Analytics. Actually this lesson has helped me sort over 40,000 rows of data in excel for credit recovery report for deliquent loans. the report was to look at deliquency per product and per employer or company from where several employees have taken credit. I even visited your linkedin profile and made you a connection. Thanks again

Can u help me with error 1004 in the below Sub Split() Dim WSWB As String Dim WSSH As String WSWB = ActiveWorkbook.Name WSSH = ActiveSheet.Name vColumn = InputBox("Enter Column Name,Split By..", "Column Selection") Columns(vColumn).Copy Sheets.Add ActiveSheet.Name = "Summary" Range("A1").PasteSpecial Columns("A").RemoveDuplicates Columns:=1, hedder:=xlYes vCounter = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To vCounter vFilter = Sheets("Summary").Cells(1, 1) Sheets(WSSH).Active ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter Cells.Copy Workbooks.Add Range("A1").PasteSpecial If vFilter "" Then ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\" & vFilter Else ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Result\Empty" End If ActiveWorkbook.Close Workbooks(WSWB).Active Next i Sheets("Summary").Delete End Sub

Hello Friend, I use the code below to separate and create other Workbooks, I use to do the following: I choose the column with a person's name, line 2 and ready, I have a workbooks for each column name I chose. But what I need is for the code to parse two sheets at the same time, and then do that split. Each new workbooks should have two sheets with information relating to a person's name. For example on sheet 1 and 2 I will have the same people, but with different information. I need a workbooks for each of these people, but with the two tabs. Can you help me by modifying the code below so I can get this result? I'd be very happy! ;) Public Sub SplitToFiles() ' MACRO SplitToFiles ' Last update: 2012-03-04 ' Author: mtone ' Version 1.1 ' Description: ' Loops through a specified column, and split each distinct values into a separate file by making a copy and deleting rows below and above ' ' Note: Values in the column should be unique or sorted. ' ' The following cells are ignored when delimiting sections: ' - blank cells, or containing spaces only ' - same value repeated ' - cells containing "total" ' ' Files are saved in a "Split" subfolder from the location of the source workbook, and named after the section name. Dim osh As Worksheet ' Original sheet Dim iRow As Long ' Cursors Dim iCol As Long Dim iFirstRow As Long ' Constant Dim iTotalRows As Long ' Constant Dim iStartRow As Long ' Section delimiters Dim iStopRow As Long Dim sSectionName As String ' Section name (and filename) Dim rCell As Range ' current cell Dim owb As Workbook ' Original workbook Dim sFilePath As String ' Constant Dim iCount As Integer ' # of documents created iCol = Application.InputBox("Enter the column number used for splitting", "Select column", 2, , , , , 1) iRow = Application.InputBox("Enter the starting row number (to skip header)", "Select row", 5, , , , , 1) iFirstRow = iRow Set osh = Application.ActiveSheet Set owb = Application.ActiveWorkbook iTotalRows = osh.UsedRange.Rows.Count sFilePath = Application.ActiveWorkbook.Path If Dir(sFilePath + "\Split", vbDirectory) = "" Then MkDir sFilePath + "\Split" End If 'Turn Off Screen Updating Events Application.EnableEvents = False Application.ScreenUpdating = False Do ' Get cell at cursor Set rCell = osh.Cells(iRow, iCol) sCell = Replace(rCell.Text, " ", "") If sCell = "" Or (rCell.Text = sSectionName And iStartRow 0) Or InStr(1, rCell.Text, "total", vbTextCompare) 0 Then ' Skip condition met Else ' Found new section If iStartRow = 0 Then ' StartRow delimiter not set, meaning beginning a new section sSectionName = rCell.Text iStartRow = iRow Else ' StartRow delimiter set, meaning we reached the end of a section iStopRow = iRow - 1 ' Pass variables to a separate sub to create and save the new worksheet CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat iCount = iCount + 1 ' Reset section delimiters iStartRow = 0 iStopRow = 0 ' Ready to continue loop iRow = iRow - 1 End If End If ' Continue until last row is reached If iRow < iTotalRows Then iRow = iRow + 1 Else ' Finished. Save the last section iStopRow = iRow CopySheet osh, iFirstRow, iStartRow, iStopRow, iTotalRows, sFilePath, sSectionName, owb.fileFormat iCount = iCount + 1 ' Exit Exit Do End If Loop 'Turn On Screen Updating Events Application.ScreenUpdating = True Application.EnableEvents = True MsgBox Str(iCount) + " documents saved in " + sFilePath End Sub Public Sub DeleteRows(targetSheet As Worksheet, RowFrom As Long, RowTo As Long) Dim rngRange As Range Set rngRange = Range(targetSheet.Cells(RowFrom, 1), targetSheet.Cells(RowTo, 1)).EntireRow rngRange.Select rngRange.Delete End Sub Public Sub CopySheet(osh As Worksheet, iFirstRow As Long, iStartRow As Long, iStopRow As Long, iTotalRows As Long, sFilePath As String, sSectionName As String, fileFormat As XlFileFormat) Dim ash As Worksheet ' Copied sheet Dim awb As Workbook ' New workbook ' Copy book osh.Copy Set ash = Application.ActiveSheet ' Delete Rows after section If iTotalRows > iStopRow Then DeleteRows ash, iStopRow + 1, iTotalRows End If ' Delete Rows before section If iStartRow > iFirstRow Then DeleteRows ash, iFirstRow, iStartRow - 1 End If ' Select left-topmost cell ash.Cells(1, 1).Select ' Clean up a few characters to prevent invalid filename sSectionName = Replace(sSectionName, "/", " ") sSectionName = Replace(sSectionName, "\", " ") sSectionName = Replace(sSectionName, ":", " ") sSectionName = Replace(sSectionName, "=", " ") sSectionName = Replace(sSectionName, "*", " ") sSectionName = Replace(sSectionName, ".", " ") sSectionName = Replace(sSectionName, "?", " ") ' Save in same format as original workbook ash.SaveAs sFilePath + "\Split\" + sSectionName, fileFormat ' Close Set awb = ash.Parent awb.Close SaveChanges:=False End Sub

Thanks for your code, the instruction is very clear. Appreciate it!

Thank you very much bro. You helped me a lot, I was scractching my head to do this job but you made it very simple. Thank you again 😂😂😂

Please share the VBA codes

Thank you! Thank you! I had a spreadsheet of 41000 lines that I needed to break into 1000 spreadsheets. This was awesome!

I also got Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter, Any solution on this

Hello Sir, I want to automate a process using vba script. We have around 1lac records of employees. Need to filter by employee name and export to. Txt file with pipe delimeter. Can you help me on this!

Hi, This code is not working for columns which contains number. Please help

HI, iam getting error "ActiveSheet.Columns.AutoFilter field:=Columns(vcolumn).Column, criterial:=vfilter" pls help me out to sort this pls........

I have an excel work book with 6 datasheets, now every data sheet has VARIABLE X and VARIABLE Y along with other data. VARIABLE X has values like "a", "b", "c" and VARIABLE Y has values like "e","f", "g". Now what i want is from this excel workbook i want to split the file into 6 sub files , first keeping only values from Variable X and then Variable Y.

Dear Sir my workbook not saving stopped with bellow code please help me ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split\" & vFilter

Is it possible to split into multiple tabs within the same workbook, based on a range of numerical values in one column? Example: All rows with values between 270xxxxxx to 279xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLA___. All rows with values between 300xxxxxx to 329xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLB___. All rows with values between 360xxxxxx to 369xxxxxx in Column D, split to a new sheet, and rename this new tab __SPLC___. ..so on and so forth.

Im having trouble with the below line of code: Run-time error '1004': AutoFilter method of Range class failed ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vFilter not sure what I need to update? Please help.

+SAF Business Analytics - Hi, I am hoping you receive this message. First, thank you so much for posting this it's wonderful I was able to execute it without issues. I just have one minor set back that I am hoping you could help me with. Instead of the information pasting into the code you wrote "Sheets.Add" then "ActiveSheet.Name = "_Summary" " is it possible for me to paste it into a preformatted template instead? If so, what would the coding be for that? Any help would be greatly appreciated! Regards,Jamilka

Here is my requirement, I want to break a huge excel file containing some 1000k rows to a standard ~60k row line items for each file with a custom rule or just copy 60K row lines and create files till the end of the rows will be completed

Hopefully you are still around, but my macro keeps failing at the AutoFilter section. I'm getting the "AutoFilter method of Range class failed" Run-Time Error '1004': No clue what's going on here, I was hoping you could elaborate on what that particular line is doing and if you had any thoughts on what might be failing for me.

Hello, Can you help me with a problem that I have after following your tutorial? Everything works but all the new files have 5mb, while I started from a file of 24kb and every file has just a row. What could be the reason?

Thanks For Video. How can we populate each file to outlook as separate email, while data is spliting.

Hi, I've tried this code and it's work for my job. But, there are two problems that happened to me: 1. after all new workbook are generated, then the workbook which is open/active is the last new splitted workbook that is generated (not the master workbook). Can I make the master workbook to keep active and open even if the new splitted woorkbook is generated? 2. Because I need to do it everyday (because the new master workbook will also be updated everyday), so I need to overwrite the splitted workbook which is generated yesterday with the new workbook that will be generated today. When I use this code, I can not overwrite the last splitted workbook with the new splitted workbook. Could you please help me with this? Thankyou in advance

I need a macro for following condition suppose i have customer excel file in which first 7 rows is for header so, from 8th rowrecords are start i need to split rows of 500 record each in one file and save them with name customer1,customer2,customer3,........ suppose i have customer file of 2540 records so it split in customer1 which have header rows with record starts from 8th row to 507th row customer2 which have header rows with record starts from 508th row to 1007th row customer3 which have header rows with record starts from 1008th row to 1507th row customer4 which have header rows with record starts from 1508th row to 2007th row customer5 which have header rows with record starts from 2008th row to 2507th row customer6 which have header rows with record starts from 2508th row to 2540th row

Hi,Thanks for the excellent step by step explanation. Do the VBA code is saved anywhere, I need to use it. Thanks in advance.

Hi, i love your video. Just need small help. I need one more workbook to be added automatically to all the sheets. Is that possible. Actually that workbook contain pivot related to the data that got split. PLease help

The title is misleading. It is splitting by columns not by number of rows.

Terrific video, thank you! How do you maintain the original formatting in the newly created sheets?

Thanks for uploading the video.. 🙏🙏

I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?

This is great script but i would like to know how to modify to just create tab's in same workbook and rename.

Thank you for the lesson. Loved it! If the name of the file that we save has a special character, it will not be saved. If we remove the special character we will not be able to filter. Here in the code we have given the file name as vFilter, but i want everything to be the same and only the file name in a format that we can save i.e ( does not include special characters).How can i do that? i removed all the special characters and pasted the names in coulmn B, but unable to use two loops. please help!

can you suggest how we can separate the data in the same workbook with multiple sheets

how do you make sure you keep the first row accross sheets? the headings"

Hi not too sure are you still answering ques here? have some ques in mind, thanks

Great explanation. It really helped me.

Can you please provide us the code to copy it directly

Getting error like vcolumn variable not defined

I need special help on this tutorial. How can I reach you

Great Video!! Thanks a lot.

Great video, thank you so much

Thank you so much, amazing!

please, we need this VBA Code

Can you post the code

Has anyone found a solution for the Run-time error 1004? I can't get pass the AutoFilter either.

Hi, I tried with your code, and it did generate multiple workbooks. However, each workbook only has the variable names in the first row, and the rest is just blank. Do you know what the problem might be? Thanks!!!

Thnx for showing how to split the data into multiple workbooks. Like you make workbooks as per division, suppose if I need to send this workbook to each divisional supervisor via outlook then what will be code & same for Gmail.

👍 excellent

Hi, some output files that were save were not in the xlsx or any excel related format. I had to add a .xlsx to open them. what could be the issue. Most were ok and results as expected except for the format of some of the data files. all data files were over 240!

I get an error message at one of the steps. After the Summary tab is created, the code pastes column A into the summary tab but the data promptly disappears (after one second) from the summary tab and this causes the next step to fail because there is no data. Why is that happening?

Where do i get code for this video.

Run-time error '1004' AutoFilter method of Range class Failed. Hi same issue as others. Code worked great up to line ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, criterial:=vFilter Tried all options does not seem to change this error. Any ideas would be welcome. Great code though! Duncan