At times, in data analysis, you may need to split the contents of an Excel worksheet into multiple Excel workbooks according to a specific column. Now, in this post, we will teach you 2 quick ways to get it.
Many users frequently need to split an Excel worksheet that contains huge rows of data into multiple separate Excel workbooks based on a specific column. For instance, here is my sample Excel worksheet. I would like to split this sheet’s data on basis of the “Price of single license (US$)” column into multiple workbooks.
In general, you’ll tend to use the following Method 1 to manually filter and copy data. But, it will be quite tedious and stupid if there are too many filter options. Therefore, here we also show a much more convenience way – Method 2, which uses VBA. Now, read on to get them in detail.
Method 1: Copy Contents to Separate Excel Workbooks after Filter
- At first, select a cell in the specific column, like “Cell B1” in my own instance.
- Then, turn to “Data” tab and click “Filter” button.
- Next, click the “down arrow” button in the column header to display a list of filter choices.
- Now, uncheck the “(Select All)” option.
- After that, you can select one filter choice, like “29.95” in my example, and click “OK”.
- At once, only the data, whose value in Column B is “29.95”, will be left.
- Then, copy the filtered data and paste them into a new Excel workbook.
- Later, use the same way to split the other data to separate Excel workbooks.
Method 2: Batch Split Contents into Multiple Excel Workbooks via VBA
- In the first place, ensure that the specific worksheet is opened.
- Next, launch VBA editor according to “How to Run VBA Code in Your Excel“.
- Then, put the following code into “ThisWorkbook” project.
Sub SplitSheetDataIntoMultipleWorkbooksBasedOnSpecificColumn() Dim objWorksheet As Excel.Worksheet Dim nLastRow, nRow, nNextRow As Integer Dim strColumnValue As String Dim objDictionary As Object Dim varColumnValues As Variant Dim varColumnValue As Variant Dim objExcelWorkbook As Excel.Workbook Dim objSheet As Excel.Worksheet Set objWorksheet = ActiveSheet nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row Set objDictionary = CreateObject("Scripting.Dictionary") For nRow = 2 To nLastRow 'Get the specific Column 'Here my instance is "B" column 'You can change it to your case strColumnValue = objWorksheet.Range("B" & nRow).Value If objDictionary.Exists(strColumnValue) = False Then objDictionary.Add strColumnValue, 1 End If Next varColumnValues = objDictionary.Keys For i = LBound(varColumnValues) To UBound(varColumnValues) varColumnValue = varColumnValues(i) 'Create a new Excel workbook Set objExcelWorkbook = Excel.Application.Workbooks.Add Set objSheet = objExcelWorkbook.Sheets(1) objSheet.Name = objWorksheet.Name objWorksheet.Rows(1).EntireRow.Copy objSheet.Activate objSheet.Range("A1").Select objSheet.Paste For nRow = 2 To nLastRow If CStr(objWorksheet.Range("B" & nRow).Value) = CStr(varColumnValue) Then 'Copy data with the same column "B" value to new workbook objWorksheet.Rows(nRow).EntireRow.Copy nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1 objSheet.Range("A" & nNextRow).Select objSheet.Paste objSheet.Columns("A:B").AutoFit End If Next Next End Sub
- After that, click the “Run” icon in the toolbar or press “F5” key button.
- When macro finishes, the separate Excel workbooks will be created with the split data from the source Excel worksheet.
- Each workbook will look like the following screenshot.
|Method 1||1. Easy to operate for all Excel users||Troublesome if there are too many filter choices|
|2. Quick if there are few filter choices|
|Method 2||Much more efficient than Method 1 no matter the amount of filter choices||A bit hard to operate for VBA newbies|
Prevent Excel Data Loss
Though MS Excel is becoming more and more advanced and sophisticated, it still tends to crash from time to time due to miscellaneous factors, such as malicious third party add-ins or human errors and so on. Since Excel crash can directly lead to Excel corruption, so as to avoid Excel data loss, you have to back up your Excel files on a regular basis. Otherwise, you need to apply an Excel repair tool, such as DataNumen Excel Repair to fix corrupted Excel files.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and outlook repair software products. For more information visit www.datanumen.com