Suppose you have a worksheet of large data that contains Name, Region, Product, ID, etc. In this case, to get data based on a particular region or Product, there is no other option rather than split data into multiple worksheets. It is safest to Split data into multiple worksheets to handle large data. In this article, we are going to show you several approaches to splitting data into multiple worksheets.
📁 Download Excel File
To practice please download the excel file from here:
Learn to Split Data into Separate Worksheets with These 3 Ways
To split data range into multiple worksheets, we considered a data set named Sales Data of Amigo Super Store- 2022 containing Seller, Products, selling quantity, and amount each month. The dataset also has 16 rows and 6 columns. To split data into multiple worksheets and workbooks we are going to use the Filter option from Top Ribbon and VBA Macro. Let’s get started.
1. Applying Filter Feature to Split into Distinct Worksheets
To split into multiple worksheets, one may use the Filter option to filter out the data based on certain criteria and copy them to another worksheet. Please follow the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the entire table.
- Secondly, navigate the Sort and Filter feature from the Data menu.
- Thirdly, select the Filter feature.
- Now, click to get the Context menu from drop-down box.
- Then, only select Banana and hit the OK button.
- Therefore, data for Banana appears only.
- Now, copy them to another worksheet.
- Similarly selecting Orange and Mango separately from the Context Menu, you will get data for Orange and Mango.
- Copying them to another worksheet, you will be able to get data for each product separately.
- However, data containing Mango showed in another Worksheet.
2. Split Data into Excel Sheets Counting Rows Utilizing VBA
Using VBA code we can split into multiple worksheets in Excel based on row. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- To enable the Developer option, Right-Click on mouse at the Top Ribbon and select Customize the Ribbon.
- Click on Developer and click on OK button.
- Now Developer mode is on.
- Then navigate the Visual Basic option.
- To insert the VBA code, click on Module from the Insert menu.
- Now insert the code in VBA module and tap the Run icon.
Sub SplitExcelSheet_into_MultipleSheets() Dim Workrange As Range Dim x_Row As Range Dim split_row As Integer Dim x_Ws As Worksheet On Error Resume Next EcelTitleId = "Split Row Numt" Set Workrange = Application.Selection Set Workrange = Application.InputBox("Range", ExcelTitleId, Workrange.Address, Type:=8) split_row = Application.InputBox("Split Row Num", ExcelTitleId, 4, Type:=1) Set x_Ws = Workrange.Parent Set x_Row = Workrange.Rows(1) Application.ScreenUpdating = False For j = 1 To Workrange.Rows.Count Step split_row resizeCount = split_row If (Workrange.Rows.Count - x_Row.Row + 1) < split_row Then resizeCount = Workrange.Rows.Count - x_Row.Row + 1 x_Row.Resize(resizeCount).Copy Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count) Application.ActiveSheet.Range("A1").PasteSpecial Set x_Row = x_Row.Offset(split_row) Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
- Then, input the dataset range $B$6:$F$16 with repeated value and hit the OK button.
- Next, Insert the Split Row Num cell and hit the OK button.
- Thus, rows are split after every 6 rows.
- Now, Split rows take place at Sheet 10 and Sheet 11 of the same workbook.
- Therefore, 6 rows are shown in Sheet 10.
- On the other hand, the Rest of the rows are shown in Sheet 11.
3. Break Worksheet into Separate Workbook Based on Column
Similarly, Using VBA code, we can split into multiple workbooks based on columns. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, Here is our dataset containing the A3:E14 range.
- Secondly, following the same steps as the previous method, insert the VBA code and tap the Run option.
Sub SplitSheetIntoMultipleWorkbooks() Dim obj_worksheet As Excel.Worksheet Dim n_last_row, n_row, n_next_row As Integer Dim str_col_value As String Dim obj_dictionary As Object Dim var_col_Values As Variant Dim var_col_Value As Variant Dim obj_Excel_Workbook As Excel.Workbook Dim obj_Sheet As Excel.Worksheet Set obj_worksheet = ActiveSheet n_last_row = obj_worksheet.Range("A" & obj_worksheet.Rows.Count).End(xlUp).Row Set obj_dictionary = CreateObject("Scripting.Dictionary") For n_row = 4 To n_last_row str_col_value = obj_worksheet.Range("C" & n_row).Value If obj_dictionary.Exists(str_col_value) = False Then obj_dictionary.Add str_col_value, 1 End If Next var_col_Values = obj_dictionary.Keys For j = LBound(var_col_Values) To UBound(var_col_Values) var_col_Value = var_col_Values(i) Set obj_Excel_Workbook = Excel.Application.Workbooks.Add Set obj_Sheet = obj_Excel_Workbook.Sheets(1) obj_Sheet.Name = obj_worksheet.Name obj_worksheet.Rows(3).EntireRow.Copy obj_Sheet.Activate obj_Sheet.Range("A3").Select obj_Sheet.Paste For n_row = 4 To n_last_row If CStr(obj_worksheet.Range("C" & n_row).Value) = CStr(var_col_Value) Then obj_worksheet.Rows(n_row).EntireRow.Copy n_next_row = obj_Sheet.Range("A" & obj_worksheet.Rows.Count).End(xlUp).Row + 1 obj_Sheet.Range("A" & n_next_row).Select obj_Sheet.Paste obj_Sheet.Columns("A:D").AutoFit End If Next Next End Sub
- In the end, we get in three different workbooks named Book10, Book11, and Book12.
How to Split Each Excel Sheet into Separate Workbooks
A Workbook containing some worksheets can be converted into separate workbooks. Please follow the process.
⬇️⬇️ STEPS ⬇️⬇️
- Similarly, Following the above process, insert the following code to create separate workbooks for each worksheet.
Sub SplitWorksheet() Dim each_sheet As String each_sheet = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each wbs In ThisWorkbook.Sheets wbs.Copy Application.ActiveWorkbook.SaveAs Filename:=each_sheet & "\" & wbs.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
- Finally, executing the VBA code, we get the .xlsx files separately for each worksheet.
How to Split Each Excel Sheet into Separate PDF Files
A Workbook containing some worksheets can be converted into separate PDF files. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Following the same steps, insert the following code to create separate PDF files for each worksheet.
Sub Split_Worksheet() Dim Each_sheet As String Each_sheet = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each wbs In ThisWorkbook.Sheets wbs.Copy Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Each_sheet & "\" & wbs.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
- Finally, executing the VBA code, we get the .pdf files separately for each worksheet.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ Change cell reference according to your worksheet.
📝 Takeaway from This Article
📌 use of VBA to split data into multiple workbooks as well as worksheets.
📌 Conversion of Excel Worksheets to separate Workbooks.
📌 Conversion of Excel Worksheets to separate PDF files.
We demonstrated every possible way to split data into multiple workbooks as well as worksheets in Excel. And, I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Also, for a better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.