Supposing you have a worksheet with huge rows of data, and now, you need to split the data into multiple worksheets based on the Name column (see following screenshot), and the names are entered randomly. Maybe you can sort them first, and then copy and paste them one by one into other new worksheets. But this will need your patience to copy and paste repeatedly. Today, I will talk about some quick tricks to solve this task. Show Split data into multiple worksheets based on column with VBA code Split data into multiple worksheets based on column with Kutools for Excel Split data into multiple worksheets based on column with VBA codeIf you want to split the data based on column value quickly and automatically, the following VBA code is a good choice. Please do as this: 1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the following code in the Module Window.
3. Then, press F5 key to run the code, and a prompt box is popped out to remind you select the header row, see screenshot: 4. And then, click OK button, and in the second prompt box, please select the column data that you want to split based on, see screenshot: 5. Then, click OK, and all data in the active worksheet is split into multiple worksheets by the column value. And the split worksheets are named with the split cell names. See screenshot: Note: The split worksheets are placed in the end of the workbook where the master worksheet is in. Split data into multiple worksheets based on column with Kutools for ExcelAs an Excel beginner, this long VBA code is somewhat difficult for us, and most of us even don't know how to modify the code as our need. Here, I will introduce you a multifunctional tool--Kutools for Excel, its Split Data utility not only can help you to split data into multiple worksheets based on column, but also can split data by rows count. Note:To apply this Split Data, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily. After installing Kutools for Excel, please do as this: 1. Select the range of data that you want to split. 2. Click Kutools Plus > Worksheet > Split Data, see screenshot: 3. In the Split Data into Multiple Worksheets dialog box, you need to: 1). Select Specific column option in the Split based on section, and choose the column value which you want to split the data based on in the drop-down list. (If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option.) 2). Then you can specify the split worksheet names, under the New worksheets name section, specify the worksheet names rules from the Rules drop down list, you can add the Prefix or Suffix for the sheet names as well. 3). Click the OK button. See screenshot: 4. Now the data are split into multiple worksheets in a new workbook. Click to Download Kutools for Excel and free trial Now! Split data into multiple worksheets based on column with Kutools for ExcelKutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now! Related article:How to split data into multiple worksheets by rows count? The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
How do I split a cell in Excel by criteria?Step 1: Select the cells you want to split into two cells. Step 2: On the Data tab, click the Text to Columns option. Step 3: In the Convert Text to Columns Wizard, if you want to split the text into the cells based on a comma, space, or other characters, select the Delimited option.
How do I extract Data from a table in Excel with multiple criteria?Extract Data From Table Based on Multiple Criteria. I. INDEX-MATCH Array Formula.. II. INDEX-MATCH Non-Array Formula.. III. INDEX-MATCH-IF Combination.. IV. LOOKUP Function.. I. INDEX-SMALL Combination.. II. INDEX-AGGREGATE Combination.. III. INDEX-MATCH-COUNTIF Combination.. IV. FILTER Function.. How do I split an Excel spreadsheet into multiple values based on column values?How to split Excel sheet into multiple worksheets. On Step 1 choose your range.. On Step 2 select one or more key columns for splitting.. On Step 3 choose destination for the resulting split tables.. On Step 4 select additional options: worksheets names, header and formatting.. Is there a way to split Data in Excel?On the Data tab, in the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard opens. Choose Delimited if it is not already selected, and then click Next. Select the delimiter or delimiters to define the places where you want to split the cell content.
|