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
Method 2: Batch Split Contents into Multiple Excel Workbooks via VBA
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
Comparison
Prevent Excel Data LossThough 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. Author Introduction: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 How do you split an Excel file based on a column?You can also split a worksheet on the columns. Select the column to the right of the column where you want to place the split, and use the Split command. You can also split on both a row and a column. by selecting the cell below and to the right of where you want the split—then click Split.
How do I split an Excel file into multiple files?Split a workbook to separate Excel Files with Move or Copy feature. Select the sheets in the Sheet tab bar, right click, and select Move or Copy from the context menu. ... . In the Move or Copy dialog, select (new book) from the To book drop down list, check the Create a copy option, and click the OK button.. How do you split columns based on cell value?Select the cell, range, or entire column that contains the text values that you want to split. On the Data tab, in the Data Tools group, click Text to Columns. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into separate columns.
How do I split an Excel sheet into multiple files using macros?Make sure macros are enabled and click Control-Shift-S. Once the file split is finished, the macro will return the location of the files, the number of rows in the file and the total number of files created.
|